Three fields table: id-data-date_time, how to get max() and date_time same time?

zxo102 ouyang wrote:

Hi everyone,
I have a table "test" which has three fields:

id data date_time
1 2 2009-10-1 12:12:12
1 10 2009-10-1 12:22:10
2 3 2009-10-1 12:10:32
2 1 2009-10-1 12:30:32

with the sql:

select max(data), id from test where 1=1 group by id

I can get

10 1
3 2

but I want corresponding "date_time" like

10 1 2009-10-1 12:22:10
3 2 2009-10-1 12:10:32

if I use the sql below

select max(data), id, date_time from test where 1=1 group by id, date_time

that won't give the correct results I want.

This seems to produce the right results:

select max(data), id, (select max(date_time) from test where id=t1.id)
from test t1 group by id;

--
Postgresql & php tutorials
http://www.designmagick.com/

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]http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT 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