-- I have added DateInserted and Address columns to show how to remove all old records for the same person

--drop table #DuplicateRow

CREATE TABLE #DuplicateRow(

ID INT identity(1,1),

DateInserted Datetime,

FName varchar(30),

LName varchar(30),

JobTitle varchar(30),

Age tinyint,

[Address] Varchar(255)

)

GO

INSERT INTO #DuplicateRow (DateInserted,FName,LName,JobTitle,Age,[Address])

SELECT DateInserted,FName,LName,JobTitle,Age,[Address] FROM (

SELECT '1/1/12' [DateInserted], 'Mangu' [FName],'Changu' [LName],'Bekar' [JobTitle], 21 [Age] , '1 broad street,ny ' [Address]UNION ALL

SELECT '2/1/12' [DateInserted],'Mangu' [FName],'Changu' [LName],'Bekar' [JobTitle], 21 [Age] , '11 broad street,ny ' [Address]UNION ALL

SELECT '6/1/12' [DateInserted],'Mehul' [FName],'Shah' [LName],'Manager' [JobTitle], 55 [Age] , '1 main street,ny' [Address]UNION ALL

SELECT '3/1/12' [DateInserted],'Mehul' [FName],'Shah' [LName],'Manager' [JobTitle], 55 [Age] , '11 main street,ny' [Address]UNION ALL

SELECT '7/1/12' [DateInserted],'Mangu' [FName],'Changu' [LName],'Bekar' [JobTitle], 21 [Age] , '11/a broad street,ny' [Address]UNION ALL

SELECT '8/1/12' [DateInserted],'Mangu' [FName],'Changu' [LName],'Bekar' [JobTitle], 21 [Age] , '11/b broad street,ny' [Address]UNION ALL

SELECT '9/1/12' [DateInserted],'Mehul' [FName],'Shah' [LName],'Manager' [JobTitle], 55 [Age] , '115 main street,ny' [Address]UNION ALL

SELECT '10/1/12' [DateInserted],'Mehul' [FName],'Shah' [LName],'Manager' [JobTitle], 55 [Age], '118 main street,ny' [Address]

) A

SELECT * FROM #DuplicateRow order by FName,LName,JobTitle,Age,DateInserted desc

-- we need to remove all rows except id= 6 and 8

SELECT

ROW_NUMBER() OVER ( PARTITION BY FName, LName, JobTitle, Age ORDER BY DateInserted desc ) R

, FName

, LName

, JobTitle

, Age

FROM #DuplicateRow

SELECT *

FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY FName, LName, JobTitle, Age ORDER BY DateInserted desc) R

, FName

, LName

, JobTitle

, Age

FROM #DuplicateRow

) B

WHERE R > 1

DELETE B

FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY FName, LName, JobTitle, Age ORDER BY DateInserted desc ) R

FROM #DuplicateRow

) B

WHERE R > 1

SELECT * FROM #DuplicateRow