SQL Server How to remove pairs which comes in sequence in 1 column only anywhere in table [closed]

x6h2sr28  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(110)

Closed. This question needs to be more focused . It is not currently accepting answers.

Want to improve this question? Update the question so it focuses on one problem only by editing this post .

Closed yesterday.
Improve this question

I just want to delete those rows which have those pairs. Pls find below Example table having (901,902) pair which are highlighted in yellow comes in sequence

I need SQL query or SP to delete those pairs only keeping the other rows which have 901. when 902 comes appears in any row, it is very sure that 901 would be there on the above row. result should look like below after removing pairs.

wz1wpwve

wz1wpwve1#

You can use a common table expression (CTE) to identify the rows you want to delete and then delete them.

;WITH CTE AS (
    SELECT
        ID,
        PO,
        [Doc date],
        Reference,
        LAG(Reference) OVER (ORDER BY [Doc date]) AS Prev_Reference
    FROM
        YourTableName
)
DELETE FROM CTE
WHERE Reference = 902 AND Prev_Reference = 901;

相关问题