Re: SELECT DISTINCT ON and ORDER BY

Osvaldo Rosario Kussama <osvaldo.kussama@xxxxxxxxx> · Fri, 28 Mar 2008 17:08:32 -0300

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general