Tags

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


SQL Puzzle | Delete Duplicate Records using Outer Join

This puzzle is really simple. You have to write a query which will delete duplicate records from a table. The sample table we are using has a single column called Id. This was asked to me at a recent technical discussion.

Please check out the sample input and the expected output below-

Sample Input

ID
1
2
2
3
3
3

Expected Output

ID
1
2
3

Rules/Restrictions

  • The solution should be should use “SELECT” statement or a CTE.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--

CREATE TABLE DeleteDuplicateUsingOuterJoin
(
	ID SMALLINT
)
GO

INSERT INTO DeleteDuplicateUsingOuterJoin
VALUES
(1),
(2),
(2),
(3),
(3),
(3)
GO

--

Solution 1 | Left Outer Join


--

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM DeleteDuplicateUsingOuterJoin
)
DELETE p
FROM CTE p
LEFT OUTER JOIN
(
	SELECT MIN(rnk) ID FROM CTE
	GROUP BY ID
)u ON u.ID = p.rnk
WHERE u.ID IS NULL

--

Also check out the other SQL Puzzles containing duplicates –

# Puzzle
1 https://msbiskills.com/2016/02/24/sql-puzzle-the-number-duplicate-puzzle/
2 https://msbiskills.com/2016/03/07/sql-puzzle-remove-duplicate-uom-puzzle/
3 https://msbiskills.com/2015/03/22/t-sql-query-find-duplicate-puzzle/
4 https://msbiskills.com/2015/03/23/t-sql-query-delete-duplicate-value-puzzle/
5 https://msbiskills.com/2015/03/25/t-sql-query-ignore-duplicate-values-while-selecting-data/

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

Advertisements