SQL-基于日期时间创建一个不同的列表

Question

我试图检索一个结果列表，其中有多行在不同的行中有重复的字段，但只想检索最近创建的行

数据

loc     |   created            |   dest    |   w   |   l   |   h          
--------------------------------------------------------------------
2       |   2020/11/09 07:00:00 |   north   |   12  |   10  |   34
3       |   2020/11/09 07:10:00 |   south   |   34  |   67  |   23
3       |   2020/11/09 08:13:00 |   west    |   67  |   22  |   12  

我已经尝试了以下操作，它确实为我提供了所需的行，但缺少了我所需的额外列。

Select loc, MAX(created)
from Data
Group By loc

要求的结果

loc     |   created            |   dest    |   w   |   l   |   h          
--------------------------------------------------------------------
2       |   2020/11/09 07:00:00 |   north   |   12  |   10  |   34
3       |   2020/11/09 08:13:00 |   west    |   67  |   22  |   12  

Answer 1

试试这个：

select *
from (
select *, row_number() over (partition by loc order by created desc) rn
from Data
) t
where rn=1

Answer 2

with max_vals as (
Select loc, MAX(created) as max_created
from Data
Group By loc
)
select d.*
from Data d join max_vals m
on d.loc = m.loc and d.created = m.max_created