Take this users and status list:

I need an SQL that will give me a list of users where the latest status is True - that will be users: 2 and 5 (user 4 latest status is False)

How can archive this?

CodePudding user response：

Use ROW_NUMBER and then filter on status is true:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY StatusDate DESC) rn
    FROM yourTable
)

SELECT UserID
FROM cte
WHERE rn = 1 AND Status = 'True';