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
