如何获得最大值的时间？

[long7411]

表：TEST
NAME TIME VALUE
A 200608 23
B 200507 25
A 200511 28
C 200408 29
B 200607 20
C 200508 12

得下结果：
Name TIME VALUE
A 200507 28
B 200507 25
C 200408 29

[long7411]

如果两个最大值相同，我只要一个，只要最近的一个，应该怎么写？

[pao1uo]

select * from test a where time=(select top 1 time from test where name = a.name order by VALUE desc,time)

[pao1uo]

最近的一个?
select * from test a where time=(select top 1 time from test where name = a.name order by VALUE desc,abs(datediff(hour,time.getdate()))

[pao1uo]

少括号

select * from test a where time=(select top 1 time from test where name = a.name order by VALUE desc,abs(datediff(hour,time.getdate())))

[九斤半]

max

[hellowork]

select * from test a where not exists(select 1 from test where name = a.name and value > a.value)

[九斤半]

create table test([NAME] char(1),[TIME] char(6),VALUE int)
insert test(name,time,value)
select 'A','200608','23' union all
select 'A','200608','23' union all--这条是增加的测试数据，如果日期相同，则都会显示出来
select 'B','200507','25' union all
select 'A','200511','28' union all
select 'C','200408','29' union all
select 'B','200607','20' union all
select 'C','200508','12'
--select * from test
select * from test a
WHERE 1>(SELECT COUNT(*) FROM test b WHERE a.time<b.time and a.name=b.name)
drop table test

[fcuandy]

select a.* from test a
inner join
(select name,max(time) mt from test group by name) b
on a.name=b.name and a.time=b.mt

[hellowork]

向楼主说明一下,我在二楼的回帖不符合楼主的要求,误解楼主的要求了,请参考其他几位朋友的帖子.

[xray2005]

不好意思，没明白LZ的目的，LZ是不是说错了啊？

表：TEST
NAME TIME VALUE
A 200608 23
B 200507 25
A 200511 28
C 200408 29
B 200607 20
C 200508 12

得下结果：
Name TIME VALUE
A 200507 28 --这个地方应该是A 200511 28吧！
B 200507 25
C 200408 29

[mugua604]

select a.* from test a
left join (select name,max(time) as max from test group by name) b
on a.name=b.name and a.time=b.max