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?
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
Scott Pletcher
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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.