Re: [GENERAL] 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-cumulative-sum-in-one-query/ (it's important 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GROUP BY, ORDER & LIMIT ?
select class, size from X t1 where size in (select size from X t2 where t2.class=t1.class order by size desc limit 5); On Tue, May 6, 2008 at 1:22 PM, Kynn Jones <[EMAIL PROTECTED]> 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. Warning, this is typed directly into mail: select class, size from X t1 where size in (select size from X t2 where t2.class=t1.class order by size desc limit 5); That should do the trick. The only problem is if you've got duplicated size values, you could end up with more than 5 per class. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GROUP BY, ORDER & LIMIT ?
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. Thanks! Kynn
