Vrftsjtryk

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?