rhythmk (7/16/2012)
weblorquins (7/16/2012)
Delete From TableNameWhere ID Not IN
{
Select MAX(ID) From TableName
Group By Col1, Col2...
}
By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.
For more Interview question Click on billow link.
But this method will not be applicable if there is no id or numeric column.
It will work ... for example
create table #Test
(EmpID VARCHAR(8),
EmpName varchar(50))
insert into #Test VALUES('abc','Daya')
insert into #Test values('abc','Daya')
insert into #Test values('abc','Daya')
;WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )
DELETE FROM Emp WHERE RNUM > 1
select EmpId, EmpName FROM #Test
Result:
abcDaya