Re: GROUP BY, ORDER & LIMIT ?

On Tue, May 06, 2008 at 01:22:30PM -0400, Kynn Jones wrote:
> Suppose table X has two columns: class (TEXT) and size (INT).  I want a
> listing showing the (up to) 5 largest values of "size" for each value of
> "class" (for some values of "class" the total number of available
records
> may be less than 5).
> What would be the simplest way to achieve such a listing?  It seems like
> something one could do with GROUP BY, ORDER, and LIMIT, but I can't
figure
> out the right syntax for this query.

get  rownum(in_code TEXT) function from
http://www.depesz.com/index.php/2007/08/17/rownum-anyone-***ulative-sum-in-one-query/
(it's im****tant to use the version with in_code argument.

then write:

select *
from
(select class, size from X order by class asc size desc) q
where rownum(class) <= 5;

depesz

-- 
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/
- blog dla ciebie (i moje CV)

-- 
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general