Re: SELECT DISTINCT ON and ORDER BY

On Fri, 28 Mar 2008, Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
>> The result in this case should be:
>>
>> id value order_field
>> 3 10 1
>> 5 12 2
>> 4 5 8
>
> SELECT id, value
> FROM (
> SELECT DISTINCT ON (value) id, value, order
> FROM table
> ORDER BY value, id) x
> ORDER BY order;

returns

id | value
----+-------
1 | 10
2 | 12
4 | 5

to get the right results, append a DESC after "id" column in ORDER
BY:

id | value
----+-------
3 | 10
5 | 12
4 | 5

BTW, if I'm not mistaken, this solution assumes an order relation
between your "id" and "value" columns.

Regards.