Return only the top row for each join

Question

This is my current query:

SELECT 
    u.UserName, s.StatusName
FROM 
    UserStatus us
    JOIN Status s on s.StatusId = us.StatusID
    JOIN Users u on u.UserId = us.UserId
WHERE 
    us.UserId in (select UserId from Users)
    AND us.DateCreated > '2017-07-14 00:00:00.000'
ORDER BY 
    us.DateCreated desc

It returns this:

How would I get only the most recent status for each user?

EDIT:

I can't just do SELECT TOP 2 because I don't know how many users there are going to be. There might be 500. There might be 10.

This seems to be duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Marek Vitek, Jul 14 '17 at 22:01

score 2 · Accepted Answer · answered Jul 14 '17 at 21:58

You can set it in the where that the Date Created is max per user Like this

SELECT 
    u.UserName, s.StatusName
FROM 
   UserStatus us
    JOIN Status s on s.StatusId = us.StatusID
    JOIN Users u on u.UserId = us.UserId
WHERE 
    us.UserId in (select UserId from Users)
    AND us.DateCreated = (SELECT MAX(DateCreated) FROM UserStatus us2 WHERE 
 us2.UserId = us.UserId)
ORDER BY 
    us.DateCreated desc

Matthew Doran · Answer 2 · 2017-07-14T21:58:29.207

0

Try changing

SELECT u.UserName, s.StatusName

to

SELECT TOP(2) u.UserName, s.StatusName

That should select only the top two rows! See https://www.w3schools.com/sql/sql_top.asp

Edit: If the top rows changes, maybe select based on a percentage of total statuses

edited Jul 14 '17 at 21:58

answered Jul 14 '17 at 21:48

Matthew Doran

1
1

Sorry my OP wording was not very clear. I edited it. – Casey Crookston Jul 14 '17 at 21:53
TOP will not work. In this case I happen to know that there are only 2 users, so SELECT TOP 2 would work. But that won't always be the case. See the edits to my OP – Casey Crookston Jul 14 '17 at 21:55
Gotcha. Guess I should have figured your initial question wasn't so straightforward. Don't think my edit will help you, because they can be in the same status in multiple fields... – Matthew Doran Jul 14 '17 at 21:57

Return only the top row for each join

2 Answers2