SQL: status flips between 1 and 2; select all statuses, which are 2 since the last time it was 1

I have write only DB log of changes I keep track of (or statuses) and values fluctuate between 1 and 2. In the below table; ID is identity column, STATUS is either 1 or 2 and USER is a user id.

If the latest status (i.e. max ID) for a given user is 1 then my query should return nothing (1 = good). So running the query against the data above would be just that.

Here comes my question: I want to query for all statuses of 2 since the last time it was 1. Here is sample data:

In this case my query should return 2 and 3 (ID) because these have statuses of 2 since the last time it was 1.

This next query should return nothing because the latest status for this user was switched to 1:

And finally this next one should return 5 (because the latest status is 2 since the last time it was 1):

There is no date field in this table, you can only work with MAX(ID) … GROUP BY ID, USER

How can I do this? I’m using MS SQL 2016.

Answer

You can use windowed aggregates to do this

WITH T
     AS (SELECT ID,
                STATUS,
                [USER],
                MAX(CASE WHEN Status = 1 THEN ID END) OVER ( PARTITION BY [USER]) AS MaxS1
         FROM   YourTable)
SELECT *
FROM   T
WHERE  Status = 2
       AND (ID > MaxS1  OR MaxS1 IS NULL)

Remove the OR MaxS1 IS NULL if you don’t want the rows returned for users that have 2 and have never had 1 as a status