CODEWITHSUNDEEP
sql-serversql-server-2008
user3709871
user3709871

Reputation: 47

Find Duplicated Records in SQL Server

how can i find duplicated records in sql server . it will be good ,if i can find the records except the latest record. because i want to delete all the records other than the latest one. i am planning to do this by putting all the records except the latest into a temp table and delete the original records based on the id of temp table

example

employeeID     EmployeeName

1                       A
2                       B   
3                       A
4                       A
5                       B

i want to select 1,3 (in case of employeeName='A') i want to select 2 in case of employeeName='B'

Upvotes: 1

Views: 134

Answers (4)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

Duplicate records of 'A'

SELECT      employeeID
FROM        Emp AS A
WHERE       EmployeeName = 'A' AND 
            employeeID <> (SELECT MAX(employeeID)
                           FROM   Emp
                           WHERE  EmployeeName = A.EmployeeName)

All Dulplicates

SELECT      employeeID
FROM        Emp AS A
WHERE       employeeID <> (SELECT MAX(employeeID)
                           FROM   Emp
                           WHERE  EmployeeName = A.EmployeeName)

To Delete Duplicate records

DELETE FROM Emp
WHERE  employeeID NOT IN (SELECT   MAX(employeeID)
                          FROM     Emp
                          GROUP BY EmployeeName)

Upvotes: 1

Mike Ante
Mike Ante

Reputation: 756

This selects all the old duplicates from example table.

SELECT * FROM example WHERE employeeID NOT IN (SELECT MAX(employeeID) _ FROM example)

Upvotes: 0

user3815462
user3815462

Reputation: 61

SELECT * FROM Table_1 WHERE employeeID not in 
(
    SELECT     MAX(employeeID)
    FROM         Table_1
    GROUP BY EmployeeName
)

Upvotes: 0

inon
inon

Reputation: 1772

First add Identity column to the original table (called employeeNo)

declare @T table(E_Name nvarchar(255), C int, Max_ID int)

insert @T
select employeeName, count(1), max(employeeNo)
from employees
group by employeeName 
having count(1)>1

delete employees
from employees
left join @T on E_Name = employeeName 
where C is not null and Max_ID <> employeeNo

Upvotes: 0

Related Questions