Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Pingback: SQL Puzzle | Delete Duplicate data from table without using Aggregate & Ranking functions | Improving my SQL BI Skills
Pingback: SQL Puzzle | Delete Duplicate Records using Outer Join | Improving my SQL BI Skills
select distinct Id from tbl
LikeLike