Re: 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/