Latest entry in a group SQL Server

Given the sample data below, I need a list of the ids whose latest entry is Rejected. Thus, I need to see id 2 because its latest is 6/4/2020 and that is Rejected. I do not want to see id 1 as its latest entry is Requested.

CREATE TABLE #temp
(
    id int, 
    mydate datetime,
    status VARCHAR(20)
)

INSERT INTO #temp VALUES (1, '6/1/2020', 'Rejected')
INSERT INTO #temp VALUES (1, '6/2/2020', 'Requested')
INSERT INTO #temp VALUES (1, '6/3/2020', 'Rejected')
INSERT INTO #temp VALUES (1, '6/4/2020', 'Requested')

INSERT INTO #temp VALUES (2, '6/1/2020', 'Requested')
INSERT INTO #temp VALUES (2, '6/2/2020', 'Requested')
INSERT INTO #temp VALUES (2, '6/3/2020', 'Requested')
INSERT INTO #temp VALUES (2, '6/4/2020', 'Rejected')

SELECT * FROM #temp

SELECT id, MAX(mydate)
FROM #temp
WHERE status = 'Rejected'
GROUP BY id

This is my pathetic attempt so far

SELECT id, MAX(mydate)
FROM #temp
WHERE status = 'Rejected'
GROUP BY id

But this will only bring back the latest date in each group. I need a list where the latest entry was Rejected. I expect the answer to be embarrassingly simple but I’m having a heck of a time with this.

Thanks

Carl

Answer

You can get this using row_number() function as shown below.

;WITH cte
AS (
    SELECT Id
        ,mydate
        ,STATUS
        ,ROW_NUMBER() OVER (
            PARTITION BY Id, status ORDER BY mydate desc
            ) row_num
    FROM #temp
    )
SELECT *
FROM cte
WHERE row_num = 1
    AND STATUS = 'Rejected'

Here is the live db<>fiddle demo.

Advertisement

Answer