从SQL Server中的表中仅获取最新记录

I have a table that holds multiple records of a particular user with datetime, my requirement is that I need only latest record through query. I don't know how to get this type of data.

我有一个表，用日期时间保存特定用户的多个记录，我的要求是我只需要通过查询的最新记录。我不知道如何获得这种类型的数据。

Table

表

Create Table tblReport(
USERID LONG, ReportDateTime Datetime
)

Current Records

当前记录

USERID          ReportDateTime 
1               2017-04-18 21:41:33.903
4               2017-04-20 01:16:00.653
4               2017-04-26 20:00:20.497
71              2017-04-17 22:31:37.437
4               2017-04-26 20:01:20.933 
225             2017-04-20 00:58:10.253
225             2017-04-25 23:09:34.433
1               2017-04-18 23:35:00.567

Desired Output

期望的输出

USERID          ReportDateTime 
1               2017-04-18 23:35:00.567
4               2017-04-26 20:01:20.933
71              2017-04-17 22:31:37.437 
225             2017-04-25 23:09:34.433

My Query

我的查询

select USERID,max(ReportDateTime) from tblReport group by USERID, ReportDateTime order by USERID

2 个解决方案

#1

Remove ReportDateTime from your group by.

从您的组中删除ReportDateTime。

select 
    UserId
  , ReportDateTime = max(ReportDateTime)
from tblReport
group by userid
order by userid

rextester demo: http://rextester.com/CLQ69624

rextester演示：http：//rextester.com/CLQ69624

returns:

收益：

+--------+-------------------------+
| UserId |     ReportDateTime      |
+--------+-------------------------+
|      1 | 2017-04-18 23:35:00.567 |
|      4 | 2017-04-26 20:01:20.933 |
|     71 | 2017-04-17 22:31:37.437 |
|    225 | 2017-04-25 23:09:34.433 |
+--------+-------------------------+

#2

Another option is using the With Ties clause

另一种选择是使用With Ties子句

Select Top 1 With Ties *
 From tblReport 
 Order By Row_Number() over (Partition By UserID Order by ReportDateTime Desc)

#1