Re: Three fields table: id-data-date_time, how to get max() and date_time same time? - Mailing list pgsql-general

On Fri, Nov 06, 2009 at 02:09:03PM +1100, Chris wrote:
> select max(data), id, (select max(date_time) from test where id=t1.id)
> from test t1 group by id;

I'd tend to use the DISTINCT ON[1] operator for these sorts of problems:

  SELECT DISTINCT ON (id) *
  FROM test
  ORDER BY id, data DESC;

--
  Sam  http://samason.me.uk/

 [1] http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT