选择每天ID的最新记录-Oracle pl SQL

2022-08-12 00:00:00 sql oracle greatest-n-per-group plsql

如何编写一条按ID每天返回最新记录的SQL语句。例.数据如下。

Id   Name        Comment         Value    DateTime
1    Tim         Test            100      02/06/2020 15:05:12
2    Sue         House           200      03/06/2020 08:25:01
1    Tim         Test            150      02/06/2020 18:05:12
3    Doug        Cars            680      10/05/2019 04:45:10
2    Sue         Tennis          200      03/06/2020 10:35:15

我会得到:

Id   Name        Comment         Value    DateTime
1    Tim         Test            150      02/06/2020 18:05:12
3    Doug        Cars            680      10/05/2019 04:45:10
2    Sue         Tennis          200      03/06/2020 10:35:15

我是否需要按最大日期时间分组的子选择查询?


解决方案

我将采用窗口函数:

select id, name, comment, value, dateTime from
(
  select id, name, comment, value, dateTime
           , last_value(dateTime) over( partition by id, trunc(datetime)
                                        order by dateTime 
             rows  BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) lv
) 
where dateTime=lv

相关文章