如何编写一条按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