Get id based on MAX(Date) for each user

Question

I'm trying to query for last read report and the date it was read.

UserReport

UserId, ReportId, DateRead
1,      2,        2018-01-01
1,      1,        2015-02-12
2,      3,        2016-03-11
3,      2,        2017-04-10
1,      3,        2016-01-01
2,      1,        2018-02-02

So to get for a specific user I can do a query like this:

SELECT TOP 1 *
FROM UserReport
WHERE UserId = 1
ORDER BY DateRead DESC

But I'm having troubles figuring out how to do this for each user. What is throwing me off is TOP 1

Expected Result:

UserId, ReportId, DateRead
1,      2,        2018-01-01
2,      1,        2018-02-02
3,      2,        2017-04-10

Answer 1

You could use:

SELECT TOP 1 WITH TIES *
FROM UserReport
ORDER BY ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY DateRead DESC)