Re: SELECT DISTINCT ON and ORDER BY

josep porres escreveu:
> maybe this?
>
> select value, max(id) as id, max(order_field) as order_field
> from mytable
> group by value
> order by 3
>

Wrong. For the op data you will obtain tuples not in original relation.

bdteste=# SELECT * FROM foo;
id | value | order_field
----+-------+-------------
1 | 10 | 3
2 | 12 | 4
3 | 10 | 1
4 | 5 | 8
5 | 12 | 2
(5 registros)

bdteste=# SELECT max(id), value, max(order_field)
FROM foo GROUP BY value
ORDER BY 3;
max | value | max
-----+-------+-----
3 | 10 | 3
5 | 12 | 4
4 | 5 | 8
(3 registros)

Try:
bdteste=# SELECT * FROM (
SELECT DISTINCT ON (value) id, value, order_field
FROM foo ORDER BY value, order_field) AS bar
ORDER BY order_field;
id | value | order_field
----+-------+-------------
3 | 10 | 1
5 | 12 | 2
4 | 5 | 8
(3 registros)

Osvaldo