Thread: Three fields table: id-data-date_time, how to get max() and date_time same time?
Three fields table: id-data-date_time, how to get max() and date_time same time?
From
zxo102 ouyang
Date:
Hi everyone,
I have a table "test" which has three fields:
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.
Can anybody give me help? Thanks a lot in advance.
ouyang
Re: Three fields table: id-data-date_time, how to get max() and date_time same time?
From
Chris
Date:
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/
Re: Three fields table: id-data-date_time, how to get max() and date_time same time?
From
Sam Mason
Date:
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