This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to delete the duplicate data of col1 by without using any RANK function.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 |
id col1 ----------- ----------- 1 1 2 1 3 2 4 2 5 3 6 4 7 5 8 5 |
Expected Output, after deleting the duplication:
|
1 2 3 4 5 6 7 |
id col1 ----------- ----------- 2 1 4 2 5 3 6 4 8 5 |
Create a sample table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE tbl_TempData ( id INT ,col1 INT ) GO INSERT INTO tbl_TempData VALUES (1,1),(2,1) ,(3,2),(4,2) ,(5,3),(6,4) ,(7,5),(8,5) GO |
Solution 1: Using Subquery
|
1 2 3 4 5 6 7 8 9 |
DELETE t FROM tbl_TempData t WHERE id NOT IN ( SELECT MAX(id) as Maxid FROM tbl_TempData td WHERE td.col1 = t.col1 GROUP BY col1 ) |
Solution 2: Using CROSS APPLY
|
1 2 3 4 5 6 7 8 9 10 |
DELETE t FROM tbl_TempData t CROSS APPLY ( SELECT TOP 1 MAX(id) a1 FROM tbl_TempData td WHERE td.col1 = t.col1 GROUP BY col1 ) td WHERE td.a1 <> t.id |
Please try the different solution for this puzzle and share it via comment...
