Remove duplicate records from table

Question

Hi,

I Found duplicate records in the table and want to remove; on the following query i found duplicate records;

SELECT *
FROM [rFAR_Aggregated_Fallout4ManualRecon]
where [rFAR_AggregationID] in
(
	SELECT [rFAR_AggregationID]
    FROM [rFAR_Aggregated_Fallout4ManualRecon]
	group by [rFAR_AggregationID]
	having count([rFAR_AggregationID])>1
)
order by rFAR_AggregationID

Answer 1

Try:

;with cte as (select *, row_number() over (partition by 
[rFAR_AggregationID] order by DateField) as Rn
    FROM [rFAR_Aggregated_Fallout4ManualRecon])

delete from cte where Rn > 1

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks. Work Fine.

Answer 3

WITH RemoveDuplicate AS
(
SELECT *, 
	 ROW_NUMBER() OVER(PARTITION BY empid, name ORDER BY empid,name) AS row 
FROM temp
)

DELETE RemoveDuplicate 
WHERE row>1