Re: Sorted group by

Matthew Wakeling wrote on 10.08.2010 18:03:
> On Tue, 10 Aug 2010, Thomas Kellerer wrote:
>> No. It's built in (8.4) and it's called Windowing functions:
>> http://www.postgresql.org/docs/8.4/static/tutorial-window.html
>> http://www.postgresql.org/docs/8.4/static/functions-window.html
>>
>> SELECT group, last_value(value) over(ORDER BY number)
>> FROM table
>
> I may be mistaken, but as I understand it, a windowing function doesn't
> reduce the number of rows in the results?

Yes you are right, a bit too quick on my side ;)

But this might be what you are after then:

select group_, value_
from (
   select group_, value_, number_, row_number() over (partition by group_ order by value_ desc) as row_num
   from numbers
) t
where row_num = 1
order by group_ desc