group by max

[a291410855]

name time colother
test1 2013-3-3 msg1
test1 2013-3-4 msg2
test2 2013-3-5 msg3
test2 2013-3-6 msg4

select name,max(time) from tb group by name

这里我还想得到colother列的信息.应该怎么写?

[--小F--]

select * from tb t where not exists(select 1 from tb  where name=t.name and time>t.time)

[--小F--]

select * from tb t where time=(select max(time) from tb where name=t.name)


select a.* from tb a inner join (select name,max(name) from tb group by name) as b on 
a.name=b.name

[唐诗三百首]

select a.*
 from tb a
 inner join
 (select name,
         max([time]) 'maxtime' 
  from tb
  group by name) b on a.name=b.name and a.[time]=b.maxtime

[灬浪子灬]

引用 3 楼 ap0405140 的回复:

select a.*
 from tb a
 inner join
 (select name,
         max([time]) 'maxtime' 
  from tb
  group by name) b on a.name=b.name and a.[time]=b.maxtime

select name,max(time) time from tb group by name 不行吗？