CODEWITHSUNDEEP
sqlsql-servert-sqlsql-delete
rahul
rahul

Reputation: 21

Delete query is not working with order by for multiple columns

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.

I tried with the following query, but it doesn't work:

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

Upvotes: 0

Views: 95

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521854

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;

Upvotes: 1

Related Questions