If we need to delete or select the duplicated records from the table, we can do it in two ways.
1. If the tables is having identity column then use the Group By with MAx() to get the list of duplicated records.
-- The Bellow Sql Statement will Display the Duplicate Records Details
SELECT *
FROM M_Site
WHERE SEQID NOT IN
(SELECT MAX(SEQID)
FROM M_Site
GROUP BY SiteName
)
-- The Bellow SQL Statement will used to delete the Duplicated recoreds
DELETE
FROM M_Site
WHERE SEQID NOT IN
(SELECT MAX(SEQID)
FROM M_Site
GROUP BY SiteName
)
If the tables doesn't have identity column the we unable to use the group by With Max() clause alone.
So we need an alternative solutions. The bellow examples uses the ROW_NUMBER() Over Partition Clause to getting the
duplicated or tiriuplicated records from the DB.
Delete from a
from
(select Emp_Name, Company, Join_Date, Resigned_Date
,ROW_NUMBER() over (partition by Emp_Name, Company, Join_Date
,Resigned_Date
order by Emp_Name, Company, Join_Date
,Resigned_Date) RowNumber
from Emp_Details) a
where a.RowNumber > 1
-- For More Please visit:http://www.sqlservercentral.com/articles/T-SQL/70807/
0 comments:
Post a Comment