SELECT DISTINCT ON and ORDER BY

["Stanislav Raskin" <sr(at)brainswell(dot)de>]

Hello everybody,

 

I have a table like this one:

 

id         value     order_field

1          10        3

2          12        4

3          10        1

4          5          8

5          12        2

 

What I want to do, is to do something like

 

SLECT DISTINCT ON (my_table.value)

my_table.id, my_table.value, my_table.order_field

FROM my_table

ORDER BY order_field

 

Hence selecting rows with distinct values, but primarily ordered by order_field, instead of value, which is requires by DISTINCT ON.

The result in this case should be:

 

id         value     order_field

3          10        1

5          12        2

4          5          8

 

How do I do this? I do need order_field in the select list to use it in the ORDER statement, which is why – as far as I can see – GROUP BY and SELECT DISTINCT are useless. Did I miss out on something?

 

Thank you in advance