返回每个 ID 的最新记录的 SQL 查询

Question

我有一个具有以下结构的表。

LocId    Value1    Value1Date                
.............................................
1        50        2012-10-20 14:21:00.000      
1        70        2012-10-21 14:21:00.000      
1        90        2012-10-22 14:21:00.000      
1        100       2012-10-23 14:21:00.000    
2        20        2012-10-20 14:21:00.000       
2        40        2012-10-21 11:21:00.000      
2        70        2012-10-22 14:21:00.000     
2        80        2012-10-23 14:21:00.000 
3        50        2012-10-20 14:21:00.000       
3        70        2012-10-21 11:21:00.000      
3        80        2012-10-22 14:21:00.000      
3        90        2012-10-23 14:21:00.000

我想要实现的是，对于每个 [LocId]，我需要最新日期时间(即 2012-10-23)的 [Value1]。返回的表应如下所示:

LocId    Value1    Value1Date                
.............................................
1        100       2012-10-23 14:21:00.000
2        80        2012-10-23 14:21:00.000
3        90        2012-10-23 14:21:00.000

有人可以帮忙吗？谢谢

Answer 1

您可以像这样在分区上使用排名:

select * from
(select locid, value1, value1date, 
  rank() over (partition by locid order by value1date desc) as rank
  from table1) t
where t.rank=1

参见 SqlFiddle