PostgreSQL – Find All Items Where the 2 Latest Statuses Meet Condition

greatest-n-per-grouppostgresqlpostgresql-9.4

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.

Best Answer

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.

Related Solutions

Postgresql – Get latest status of an item in the database

Use Partition by to get last record where Status is failed.

    ---Test table
    CREATE TABLE [dbo].[items](
        [id] [int] NULL,
        [Item-FK] [int] NULL,
        [timestamp] [datetime] NULL,
        [status] [varchar](200) NULL
    ) ON [PRIMARY]
    GO

--dummy data 
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (1, 123, CAST(0x0000A3E0006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (2, 123, CAST(0x0000A3E1006DB876 AS DateTime), N'FAILED')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (3, 124, CAST(0x0000A3E1006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (4, 124, CAST(0x0000A3E1006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (5, 125, CAST(0x0000A3E1006DB876 AS DateTime), N'FAILED')

--- Query to get all items who's last status is 'Failed'
    SELECT  *
    FROM    items
    WHERE   [Item-FK] IN (
            SELECT  [Item-FK]
            FROM    ( SELECT    row_number() OVER ( PARTITION BY [Item-FK] ORDER BY [timestamp] DESC ) iid,
                                *
                      FROM      items
                    ) item
            WHERE   iid = 1 AND [status] ='FAILED')

EDIT: you can use EXISTS instead on IN

SELECT  *
FROM    items itm
WHERE   EXISTS ( SELECT [Item-FK]
                 FROM   ( SELECT    row_number() OVER ( PARTITION BY iitm.[Item-FK] ORDER BY iitm.[timestamp] DESC ) iid,
                                    *
                          FROM      items iitm
                        ) item
                 WHERE  item.iid = 1
                        AND item.[status] = 'FAILED'
                        AND itm.[Item-FK] = item.[Item-FK] )

SQL Query – Find Parent Records Where Child Records Match Specific Set

The problem we are trying to solve is called exact relational division. It requires an extra condition from the relational division and there are still a lot (too many actually) ways to solve this kind of problem.

Check this related SO question where you will find more than 10 different ways to do this in Postgres and performance tests. The problem discussed there is (simple, not exact) relational division so you'll have to adjust the answers:

How to filter SQL results in a has-many-through relation

Here is another one:

select p.*
from item p 
  join 
    ( select item_id from item_detail 
      where (type, value) = (10, 1000)
      intersect
      select item_id from item_detail 
      where (type, value) = (20, 2000)
      except
      select item_id from item_detail 
      where (type, value) not in ((10, 1000), (20, 2000))
    ) as c
  on c.item_id = p.id ;

I suggest an index on (type, value, item_id).

Best Answer

Related Solutions

Postgresql – Get latest status of an item in the database

SQL Query – Find Parent Records Where Child Records Match Specific Set

Related Question