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'