Query for records only where the Project Status has changed-CodePudding

I'm trying to write a query where I return only the records where the status of a project changes. Projects start off "In Progress," can "Pause," go back "In Progress," and continue until they are "Completed." The table is designed for the full history of modifications toward the project and details of the project cause a new record to be created. So, table structure will look like below, and the highlighted records are what I want to query for:

I've tried to use a combination of ROW_NUMBER() and RANK() with certain elements but I can't get seem to get it right. How do I query for the records where the status changes? This post and this post are similar but don't help.

CodePudding user response：

Thanks to @klin for the LAG() recommendation.

WITH l as (
    SELECT LAG(status, 1) OVER (PARTITION BY mod_id ORDER BY mod_date) lag, *
    FROM projects_table
    ORDER BY mod_id, mod_date
)

SELECT *
FROM l
WHERE lag IS DISTINCT FROM status
ORDER BY mod_date