Select all rows with same ID when the last record of that ID meets the criteria 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 &quot;Test&quot;




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 &quot;Open&quot; in the database.
What I want is all the rows (ID) with status&quot;Open&quot; (or whatever condition) and all their records if the last record meets the conditions..
For example for status &quot;Open&quot; 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. --&gt; 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.
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)