問題描述

我有一個具有以下結構的表格.

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

我想要實現(xiàn)的是，對于每個 [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

這篇關于用于返回每個 ID 的最新記錄的 SQL 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！