Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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