Postgresql – Select all rows with same ID when the last record of that ID meets the criteria

postgresqlquery

I need help with a SQL query.
I want to return all rows when the last record (date) of the ID match the criteria.
Here is an example :

Let's call this table "Test"

ID date status
1 10/01/2021 Closed
1 09/01/2021 Resolved
1 08/01/2021 Awaiting Input
1 01/01/2021 Open
2 15/01/2021 Open
2 16/01/2021 Awaiting Input
2 17/01/2021 Open
3 07/01/2021 Open
3 08/01/2021 Awaiting Input

What i want to do is to return all the rows with the same ID if the last record (date) of that ID match the criteria.

I tried :

 SELECT * FROM test AS r    
 WHERE r.ID in (SELECT 
    ID FROM test
    WHERE status = 'Open');

But that will return all the records (ID) having "Open" in the database.
What I want is all the rows (ID) with status"Open" (or whatever condition) and all their records if the last record meets the conditions..

For example for status "Open" the result should be:

ID date status
2 15/01/2021 Open
2 16/01/2021 Awaiting Input
2 17/01/2021 Open

Indeed, the last record (ID) 17/01/2021 as status 'Open' so it match the condition. –> It could be order by date desc or asc … It's just an example.

How can I dot it?
It seems easy on paper but i'm struggling with this one.

Note that I'm using postgresql.

Thanks for your help.

Best Answer

You can accomplish this with a window function like ROW_NUMBER() like so:

WITH CTE_Test_Sorted AS
(
    SELECT ID, date, status, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) AS SortId -- Generates a unique ID (assuming each date is unique for each ID) for each row within an ID sorted by date descending
    FROM Test
),
CTE_Test_Latest AS
(
    SELECT ID, date, status
    FROM CTE_Test_Sorted
    WHERE SortId = 1 -- Filters out everything but the latest status row for each ID
)

SELECT T.ID, T.date, T.status
FROM Test T
INNER JOIN CTE_Test_Latest L
    ON T.ID = L.ID -- Filters Test on just the IDs whose latest status is the value in the WHERE clause below
WHERE L.status = 'Open' -- Filters on only IDs whose latest status = Open
Related Question