mjhjmtu

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' & 'PENDING' like displayed in the sample above - 'PENDING' latest and 'EXPIRED' immediately before that.

You could do it like this to avoid needing to hardcode row numbers into your query:

select * 
 from t as a 
 where status = 'pending' -- must be pending
 and exists
 (select * 
 from t as b
 where status = 'expired' -- must have expired
 and a.itemfk = b.itemfk
 and b.id = 
 (select max(c.id) -- must be the previous entry before pending
 from t as c
 where c.itemfk = b.itemfk
 and c.id < a.id) -- must have expired before pending
 );

This method performs a lookup to get the maximum id of the entry prior to it being pending and then checks that it is the row marked as expired.

db<>fiddle example.

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<>fiddle here

Assuming ..

• an item table with exactly 1 row per item of interest (as indicated by the column name item_fk (= "Item-FK").


• that "last" is defined by the column ts(= timestamp) - which is NOT NULL. (Might alternatively be id.)


• that (item_fk, ts) is defined UNIQUE (else you need more ORDER BY expressions to make it deterministic)

Then this should be fastest and simplest:

SELECT i.* -- or what you need from item
FROM item i
JOIN LATERAL (
 SELECT ARRAY (
 SELECT status
 FROM tbl t
 WHERE t.itemfk = i.item_id
 ORDER BY ts DESC
 LIMIT 2
 ) AS last2_stat
 ) t ON t.last2_stat = 'PENDING, EXPIRED'::text;

db<>fiddle here

You can return columns from table item directly.

It is simple to adapt to any other terminal sequence of statuses.

Have this index for best performance:

CREATE INDEX ON tbl (itemfk, id DESC, status);

status as last index expression only if you get index-only scans out of it.

Related, with more explanation:

• Optimize GROUP BY query to retrieve latest record per user


• Select first row in each GROUP BY group?


• What is the difference between LATERAL and a subquery in PostgreSQL?


• Why is array_agg() slower than the non-aggregate ARRAY() constructor?