Find all items where the 2 latest statuses meet condition

Question

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.

Is "last" defined by timestamp or ID? Is there also a table items with with 1 row per item of interest (like "FK" seems to suggest)? And how many rows per item on average? — Erwin Brandstetter, Dec 24 '18 at 03:42

score 2 · Answer 1 · answered Dec 23 '18 at 00:00

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

Could u give me a generic solution? I use Hibernate in java and need a simpler query to execute — Barcelona, Dec 23 '18 at 00:34

score 1 · Accepted Answer · answered Dec 23 '18 at 00:44

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.

I don't see how does this check that the latest status is 'pending'. — ypercubeᵀᴹ, Dec 24 '18 at 05:26

score 1 · Answer 3 · answered Dec 24 '18 at 04:19

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:

Find all items where the 2 latest statuses meet condition

3 Answers3

Linked