I have a postgresql table of tasks with various status of the task like this in table below. where in my last status of the task will be “Deleted”
I want to get the previous status of each task before it is converted as “Deleted” using Postgresql query.
We can use the LAG() window function here along with exists logic:
WITH cte AS (
SELECT *, LAG(Status) OVER (PARITION BY ID ORDER BY Updated) AS Old_Status
FROM yourTable
)
SELECT t1.ID, t1.Old_Status, t1.Status
FROM cte t1
WHERE t1.Status = 'Deleted' AND
NOT EXISTS (
SELECT 1
FROM yourTable t2
WHERE t2.ID = t1.ID AND
t2.Date > t1.Date
);
The LAG() function finds, for each ID, the immediately preceding job status. The exists clause ensures that any matching record returned is the latest one, which also has delete as the status.