1

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.

3 Answers 3

1

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
2
  • 1
    Wow, thank you very much @J.D. for the query and the explanations ! working perfect too.
    – Swennys
    Feb 8, 2021 at 20:24
  • @Swennys Sure thing, no problem, glad to help! Feel free to upvote and/or mark as accepted so other users with similar questions see it verified as helpful and accurate.
    – J.D.
    Feb 8, 2021 at 20:27
1

You can use sub selects instead of With clauses But it does the same as the others.

If first picks the last date for every ID and test it, if the status is "open", if that is true, The outer Select will get all data for that ID

CREATE TABLE table1
    ("ID" int, "date" varchar(10), "status" varchar(14))
;
    
INSERT INTO table1
    ("ID", "date", "status")
VALUES
    (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')
;
SELECT
    "ID", "date", "status"
FROM table1
WHERE "ID" IN (
               SELECT
                   "ID"
               FROM table1 t
                WHERE "date" = (SELECT MAX("date") FROM table1 
                                WHERE "ID" = t."ID") 
                                AND "status" = 'Open')
ORDER BY "date" 
ID | date       | status        
-: | :--------- | :-------------
 2 | 15/01/2021 | Open          
 2 | 16/01/2021 | Awaiting Input
 2 | 17/01/2021 | Open          

db<>fiddle here

1
  • Thank you @nbk working fine and it's shorter. Perfect explanations and example !
    – Swennys
    Feb 9, 2021 at 7:52
0

try with this:

with
last_row as (
select
ID,
max(date) as date
from test
group by ID
),

last_row_open as (
select
ID
from test
where ID in (select ID from last_row)
  and status='Open'
)

select
*
from test
where ID in (select ID from last_row_open)
4
  • Thanks for the quick reply but not working. I'm getting the error : ERROR: column "test.ID" must appear in the GROUP BY clause or be used in an aggregate function
    – Swennys
    Feb 8, 2021 at 19:59
  • Sorry @Swennys I forgot the "GROUP BY". I just edited the instruction. Try now Feb 8, 2021 at 20:04
  • can you please add always some explanation to the query s that others can use it too
    – nbk
    Feb 8, 2021 at 20:18
  • Excellent working fine ! Thank you @Paolo Finardi
    – Swennys
    Feb 8, 2021 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.