asked on

Delete duplicate rows SQL

I have a table with the following columns,
ProductID, BusDate, Code, LastUpdatedDate

I need to delete duplicate rows and just keep a row with the highest timestamp

I tried this query to give me the timestamp that I want to keep and delete the rows that have the same
ProductID, BusDate, Code but a LastUpdatedDate not equal to the max(LastUpdatedDate)

select ProductID, BusDate, Code, MAX(LastUpdatedDate), COUNT(*)
FROM Products
GROUP BY ProductID, BusDate, Code
HAVING COUNT(*) > 1

3097      2014-03-01 00:00:00.000      COUNTRY            2014-03-11 09:24:06.983            4
3097      2014-03-01 00:00:00.000      INTERNET      2014-03-11 09:24:06.983            4
3099      2014-03-01 00:00:00.000      COMMEQP            2014-03-11 09:24:06.983            4
3099      2014-03-01 00:00:00.000      COUNTRY            2014-03-11 09:24:06.983            4
3115      2014-03-01 00:00:00.000      BANKS            2014-03-11 09:24:06.983            3
3115      2014-03-01 00:00:00.000      COUNTRY            2014-03-11 09:24:06.983            4

ASKER CERTIFIED SOLUTION

jayakrishnabh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PadawanDBA

You could probably use something similar to:

with duplicateRows as
(
	select
		ProductID,
		ROW_NUMBER( ) over( partition by productID, busdate, code order by lastUpdatedDate desc ) as rowNum
	from
		Products
)

delete duplicateRows
	where rowNum > 1;

Open in new window