Re: SELECT DISTINCT ON and ORDER BY

On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
> id value order_field
> 1 10 3
> 2 12 4
> 3 10 1
> 4 5 8
> 5 12 2
>
> 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?

ORDER BY's in conjunction with DISTINCT ON are used to specify which
values you want for the other expressions in your query. For example
for value 10, do you want id to be 1 or 2, and should the order be from
the same row, or something else.

You're additionally wanting to order by the "order" column, which you
need to express as another step, i.e. a subselect something like:

SELECT id, value
FROM (
SELECT DISTINCT ON (value) id, value, order
FROM table
ORDER BY value, id) x
ORDER BY order;

No programming language will ever do exactly what you want straight
away, it's a matter of using the tools it gives you.

Sam