Postgresql – Get latest status of an item in the database

greatest-n-per-grouppostgresql

I have this table:

ID|Item-FK |timestamp|status
=============================
1 | 123    | ...     | OK
2 | 123    | ...     | FAILED
...

I want to query get all items which last status was FAILED.

If an item failed yesterday, and is OK today, I don't want to see it.

Best Answer

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] )
Related Question