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

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 <kynnjo@gmail.com> 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
david.t.wilson@gmail.com

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

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...-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