Search Postgresql Archives

Re: GROUP BY, ORDER & LIMIT ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux