Delete query is not working with order by for multiple columns-CodePudding

CREATE TABLE SAMPLE1(CN VARCHAR(MAX),CR VARCHAR(MAX), DN VARCHAR(MAX),DR VARCHAR(MAX),DMR VARCHAR(MAX))

INSERT INTO SAMPLE1 VALUES('C1','A','D1','--','--')
INSERT INTO SAMPLE1 VALUES('C1','B','D1','-A','--')
INSERT INTO SAMPLE1 VALUES('C1','E','D2','--','--')
INSERT INTO SAMPLE1 VALUES('C1','C','D1','-A','--')
INSERT INTO SAMPLE1 VALUES('C1','D','D3','--','--')
INSERT INTO SAMPLE1 VALUES('C1','F','D2','--','--')
INSERT INTO SAMPLE1 VALUES('C1','F','D2','-A','--')

Expected Result: ('C1','F','D2','-A','--') i.e last record from SAMPLE1

Tried with following query but doesn't worked:

DELETE t
FROM   (SELECT row_number()
                 OVER(
                   Partition BY CN
                   ORDER BY CR, DR, DMR DESC ) AS r
        FROM   SAMPLE1) t
WHERE  r > 1

CodePudding user response：

Given that you are using SQL Server, you could place the logic from your current query into a deletable CTE:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CN
                                 ORDER BY CR DESC, DR DESC, DMR DESC) rn
    FROM SAMPLE1
)

DELETE
FROM cte
WHERE rn > 1;

CodePudding user response：

Use this instead:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (Order by (select null) desc) rn
    FROM SAMPLE1
)
DELETE
FROM cte
WHERE rn > 1;