This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output for deleting the duplicate data by giving an ID. The table doesn’t have any primary key and writes logic without using ROW_NUMBER().
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 |
ID Name ----------- ---------- 1 ABC 1 ABC 1 ABC 3 ERZ 3 ERZ 4 HYU 5 BNM 7 WER 7 WER |
Expected Output:
|
1 2 3 4 5 6 7 8 9 |
ID Name ----------- ---------- 1 ABC 3 ERZ 3 ERZ 4 HYU 5 BNM 7 WER 7 WER |
Create a sample table:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_TestTable (ID INT, Name VARCHAR(10)) INSERT INTO tbl_TestTable VALUES (1,'ABC'),(1,'ABC'),(3,'ERZ') ,(4,'HYU'),(5,'BNM'),(1,'ABC') ,(7,'WER'),(3,'ERZ'),(7,'WER') |
Solution:
|
1 2 3 4 5 |
;WITH CTE AS ( SELECT COUNT(1) AS IDCount, ID FROM tbl_TestTable GROUP BY ID ) DELETE TOP((SELECT IDCount FROM CTE WHERE ID = 1) - 1) FROM tbl_TestTable WHERE ID = 1 |
Please try the different solution for this puzzle and share it via comment...
