CODEWITHSUNDEEP
sqlsql-servert-sql
ANJYR
ANJYR

Reputation: 2623

Get only latest records from table in 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

Upvotes: 1

Views: 110

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

Another option is using the With Ties clause

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

Upvotes: 1

SqlZim
SqlZim

Reputation: 38073

Remove ReportDateTime from your group by.

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

rextester demo: 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 |
+--------+-------------------------+

Upvotes: 2

Related Questions