Re: SELECT DISTINCT ON and ORDER BY

On Fri, 28 Mar 2008, Sam Mason <sam@samason.me.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.