Tags

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


T-SQL Query | [ Delete Duplicate Value Puzzle ] – In this puzzle we have to delete the duplicate values from the input table. Please check out the sample input and expected output for details.

Sample Input

ID
1
2
1

Expected Output

ID
1
2

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--CREATE TABLE
CREATE TABLE DeleteDup
(
ID INT
)
Go

--Insert Data
INSERT INTO DeleteDup VALUES(1),(2),(1)

--Verify Data
SELECT ID FROM DeleteDup

Update May 14 | Solution


--

/************   SOLUTION 1         ****************/



WITH CTE AS
(
       SELECT *, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY ID ) RNK FROM DeleteDup    
)
DELETE FROM CTE WHERE RNK > 1




--

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

Keep Learning

http://MSBISkills.com