sql - SQL - 获取最近的记录

Question

我有一个table：

Id   |   Name   |   Date
-----+----------+-----------------------
1    | name1    | 2021-02-10 13:00:00.000
-----+----------+-----------------------
1    | name2    | 2021-02-10 05:00:00.000
-----+----------+-----------------------
1    | name3    | 2021-02-09 14:00:00.000
-----+----------+-----------------------
2    | name4    | 2021-01-02 03:00:00.000
-----+----------+-----------------------
2    | name5    | 2021-01-07 11:00:00.000
-----+----------+-----------------------
2    | name6    | 2021-01-07 08:00:00.000

我需要获取Date每个组的最新记录（仅日期而不考虑时间）Id：

Id   |   Name   |   Date
-----+----------+-----------------------
1    | name1    | 2021-02-10 13:00:00.000
-----+----------+-----------------------
1    | name2    | 2021-02-10 05:00:00.000
-----+----------+-----------------------
2    | name5    | 2021-01-07 11:00:00.000
-----+----------+-----------------------
2    | name6    | 2021-01-07 08:00:00.000

标签： sqlsql-server

Answer 1

一种方法使用RANK()：

SELECT T.ID, T.NAME, T.DATE
FROM (SELECT t.*
             RANK() OVER (PARTITION BY ID ORDER BY CONVERT(DATE, DATE) DESC) AS SEQNUM
      FROM T
     ) T
WHERE T.SEQNUM = 1;