websss
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
;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
ASKER
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.