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