Monday, 4 May 2015

How to delete duplicate rows table in sql

Deleting duplicate rows

e.g:- Suppose that we have a table named "Employees" having auto increment id and we want to delete latest duplicate row. In the following table we would like to delete the latest duplicate salary row, which in this case is [id=3,name=f,salary=200]




DELETE FROM [Data].[dbo].[employees] 
WHERE  id IN (SELECT TOP(1)id 
              FROM   [Data].[dbo].[employees] 
              WHERE  salary IN (SELECT salary 
                                FROM   [Data].[dbo].[employees] 
                                GROUP  BY salary 
                                HAVING Count(*) > 1) 
              ORDER  BY id DESC) 

After executing this query you will get a table without any duplicate salary



No comments:

Post a Comment