问题描述
我有一个具有以下结构的表格.
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