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

问题描述

我有一个具有以下结构的表格.

I have a table with the following structure.

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].返回的表应如下所示:

What I want to achieve is that, for each [LocId], I need the [Value1] of the latest datetime (i.e. 2012-10-23). The returned table should look like:

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

有人可以帮忙吗?谢谢

推荐答案

你可以像这样使用 rank over partition:

You can use rank over partition like so:

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