How to check if a date field in a row is older than the previous row-CodePudding

I have this table with some transactions with the ID, date and amount:

TransID	Date	Amount
1494808	2022-06-04 16:44:56.700	50
1494809	2022-06-04 15:44:56.700	60

See transactionID 1494809, the date is older than 1494808. Basically, what I need is to do a search for transactions like this in the table and then insert it into a temp table so the customer can track these weird transactions in the system. Which is the easiest way to do this?

CodePudding user response：

Use LAG (or LEAD) to compare rows' values, like so:

WITH withLag AS (

    SELECT
        TransId,
        "Date",
        Amount,
        LAG( TransId, 1, NULL ) OVER ( ORDER BY TransId ) AS PrevTransId,
        LAG( "Date" , 1, NULL ) OVER ( ORDER BY TransId ) AS PrevTransDate,
        LAG( Amount , 1, NULL ) OVER ( ORDER BY TransId ) AS PrevAmount
    FROM
        table
)
SELECT
    *
FROM
    withLag
WHERE
    PrevTransId < TransId
    AND
    PrevTransDate > "Date"
ORDER BY
    TransId

SQLFiddle.com: