Find all items where the 2 latest statuses meet condition I have a table in a PostgreSQL database like this:

ID|Item-FK |timestamp|status
=============================
1 | 123    | ...     | EXPIRED
2 | 123    | ...     | PENDING
...


I want to query all items where the last two statuses are 'EXPIRED' &amp; 'PENDING' like displayed in the sample above - 'PENDING' latest and 'EXPIRED' immediately before that.
CTE add a row number in descending order, that means that row (1) must be PENDING and row (2) must be EXPIRED.

Then you can use 2 EXISTS subqueries in the WHERE clause to ensure both conditions are true.

with ct as
(
select
    id, itemfk, dt, status,
    row_number() over (partition by itemfk order by itemfk, dt desc) rn
from
    t
order by
    itemfk, dt
)
select 
    t1.itemfk
from
    ct t1
where exists (select 1 
              from ct 
              where itemfk = t1.itemfk 
                    and rn = 1 and status = 'pending')
      and
      exists (select 1 
              from ct 
              where itemfk = t1.itemfk 
                    and rn = 2 and status = 'expired')
group by t1.itemfk;


db&lt;>fiddle here