2

I want to delete duplicate rows from a table. I have searched for the query online, and found the following sample:

DELETE 
FROM dbo.LeadsContacts 
WHERE Sr_No IN
(
    SELECT Sr_No 
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) 
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

I don't understand the flow and working of the query. Can somebody please explain it works?

closed as off-topic by Colin 't Hart, Paul White, RLF, Shanky, Marian May 24 '15 at 9:46

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, RLF, Shanky, Marian
If this question can be reworded to fit the rules in the help center, please edit the question.

4

RANK() OVER (PARTITION BY xxx, yyy ORDER BY zzz) provides a number ranking for each row where xxx and yyy are identical, and orders that list by zzz

The sub-query contained in your question:

SELECT Sr_No 
FROM (
    SELECT *,
    [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) 
    FROM dbo.LeadsContacts
) T
WHERE T.Rank>1

returns a list of Sr_No values where the corresponding rows have duplicate Email and Campaign_Name column values, excluding the most-recent row. The most recent row is excluded by the WHERE T.Rank>1 statement, in combination with the RANK() ... ORDER BY clause return rows sorted in Sr_No reverse order (the DESC) keyword.

I used the following code to test your query against a small sample of rows:

USE tempdb;

CREATE TABLE dbo.LeadsContacts
(
    Sr_No INT NOT NULL
    , Email VARCHAR(255) NOT NULL
    , Campaign_Name VARCHAR(255) NOT NULL
);

INSERT INTO dbo.LeadsContacts (Sr_No, Email, Campaign_Name)
VALUES (1, 'test@test.com', 'campaign1')
    , (2, 'test@test.com', 'campaign1')
    , (3, 'test@test.com', 'campaign1')
    , (4, 'test@test.co.uk', 'campaign1')
    , (5, 'test@test.co.uk', 'campaign1')
    , (6, 'test@test.com', 'campaign1')
    , (7, 'test@test.co.uk', 'campaign2');


SELECT *
FROM dbo.LeadsContacts;

DELETE 
FROM dbo.LeadsContacts 
WHERE Sr_No IN
(
    SELECT Sr_No 
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) 
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

SELECT *
FROM dbo.LeadsContacts;

--DROP TABLE dbo.LeadsContacts;

The only rows left in the table are:

    5, 'test@test.co.uk', 'campaign1'
    6, 'test@test.com', 'campaign1'
    7, 'test@test.co.uk', 'campaign2'

Not the answer you're looking for? Browse other questions tagged or ask your own question.