Link to home
Create AccountLog in
Avatar of websss
websssFlag for Kenya

asked on

delete duplicate data

I need to delete duplicate data
i.e. if i have 2 rows exactly the same, delete one of the rows

I have a table called Tbl_Data
It has these columns

ID
GPSDateTime (datetime)
ReportID (int)
DeviceID (int)

If all 3 columns match data (except ID) I want to delete one of the rows so i'm left with unique data rows instead duplicates

how might i Do this?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of websss

ASKER

Thanks Scott

I'm getting the errror:

Msg 4112, Level 15, State 1, Line 2
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.
Avatar of websss

ASKER

Got it thanks
Yeah, sorry, I left out the ORDER BY, which must appear, even if it's meaningless:

;WITH cte_dups AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY GPSDateTime, ReportID, DeviceID ORDER BY GPSDateTime) AS row_num
    FROM Tbl_Data
)
DELETE FROM cte_dups
WHERE row_num > 1