SQL Puzzle | Delete Duplicate data from table without using Aggregate & Ranking functions

This is a variation in the Duplicate Data Puzzle . In this puzzle you have to delete duplicate data from the table without using any Aggregate function or ranking function.

Please check out the sample input values and sample expected output below.

Sample Input

a b
1 1
2 1
3 2
4 2
5 3

Expected Output

a b
2 1
4 2
5 3

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE DeleteDuplicateWithOut ( a INT ,b INT ) GO INSERT INTO DeleteDuplicateWithOut VALUES (1,1), (2,1), (3,2), (4,2), (5,3) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution 1 , Using Sub Query and ANY keyword

 ``` -- DELETE j FROM DeleteDuplicateWithOut1 j WHERE j.a <> ANY ( SELECT TOP 1 a FROM DeleteDuplicateWithOut1 e WHERE e.b = j.b ) -- ```

Solution 2

 ``` -- DELETE tbl1 FROM DeleteDuplicateWithOut1 tbl1 CROSS APPLY ( SELECT TOP 1 MAX(a) a1 from DeleteDuplicateWithOut1 tbl WHERE tbl.b = tbl1.b GROUP BY b ) tbl WHERE tbl.a1 <> tbl1.a -- ```

Solution 3

 ``` -- DELETE tbl1 FROM DeleteDuplicateWithOut1 tbl1 WHERE a NOT IN ( SELECT MAX(a) a1 from DeleteDuplicateWithOut1 tbl WHERE tbl.b = tbl1.b GROUP BY b ) -- ```

Solution 4

 ``` -- DELETE tbl1 FROM DeleteDuplicateWithOut1 tbl1 CROSS APPLY ( SELECT TOP 1 a a1 from DeleteDuplicateWithOut1 tbl WHERE tbl.b = tbl1.b )tbl WHERE tbl.a1 <> tbl1.a -- ```

Solution 5

 ``` -- DELETE D from DeleteDuplicateWithOut1 D Where Exists(Select TOP 1 1 From DeleteDuplicateWithOut1 D1 Where D.b = D1.b and D.a < D1.a) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com