Solved

delete duplicate data

Posted on 2016-09-23
4
49 Views
Last Modified: 2016-09-26
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?
0
Comment
Question by:websss
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
For best performance, if the table has an index with all three of those columns in it, and is keyed by one or more of them, start with column first in the PARTITION BY.
For example, say there was an index on ( ReportID, GPSDateTime ) that included ( DeviceID ), then you would ORDER BY ReportID, GPSDateTime, DeviceID.  The idea is to use any existing "pre-sorting" as much as possible.


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

Author Comment

by:websss
Comment Utility
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.
0
 

Author Closing Comment

by:websss
Comment Utility
Got it thanks
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now