Remove duplicates on a Table
Hi there,
I have a table that it continuos grow drastically due to an error on an automatic import. I cant control that but I can clean the table removing the duplicates. What would be the best approach?
here is the query that returns the records:
I know the RecordId is unique so if I remove that on the select I will give me the records I need but I need to remove them for good.
The Printime is a date time field. but there is not way that records will have the same time, DocumentName ...
I hope this is enough information
Thanks!
I have a table that it continuos grow drastically due to an error on an automatic import. I cant control that but I can clean the table removing the duplicates. What would be the best approach?
here is the query that returns the records:
SELECT DISTINCT [RecordID]
,[PrintTime]
,[PrintBy]
,[PrintPages]
,[PrintCopies]
,[Printer]
,[DocumentName]
,[Client]
,[PaperSize]
,[Language]
,[Height]
,[Width]
,[Duplex]
,[Grayscale]
,[size]
FROM [[PrinterLog]
I know the RecordId is unique so if I remove that on the select I will give me the records I need but I need to remove them for good.
The Printime is a date time field. but there is not way that records will have the same time, DocumentName ...
I hope this is enough information
Thanks!
Last Comment
Vitor Montalvão
What defines a duplicate record? i.e. which columns should be compared so we know that we're facing a duplicate record?
ASKER
here is a sample of the data.
All of these are duplicate records where I only need one.
All of these are duplicate records where I only need one.
PrintTime PrintBY PrintPages PrintCopies Printer DocumentName Client PaperSize Language Duplex Grayscale size
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
2017-01-29 06:50:09.000 jgonzalez 1 1 COLOR SHARP MX-C311 PCL6 document 06570COH Letter PCL6 NOT DUPLEX NOT GRAYSCALE 382kb
ASKER
notice how all those records have the same PrintTime, PrintBy, ...
Good. Which one of those should be preserved?
ASKER
only one row entry per duplicate
I understood that but which one to keep? I mean, if I run a DELETE command it will remove all rows. Is there any column that can be used to differentiate the duplicated rows so it can delete all less one row?
ASKER
Vitor I can not do DELETE command as it will delete all my records and I will not keep anything.
lets me explain a bit more:
I I run the above query and I add a where clause 'Where Printtime = '01/29/2017''
I will get something like this:
But if I do SELECT DISTINCT I will get just this:
lets me explain a bit more:
I I run the above query and I add a where clause 'Where Printtime = '01/29/2017''
I will get something like this:
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
...
But if I do SELECT DISTINCT I will get just this:
2017-01-29 06:50:09.000, jgonzalez, 1, 1, COLOR SHARP MX-C311, PCL6, document, 06570COH, Letter, PCL6, NOT DUPLEX, NOT GRAYSCALE, 382kb
How big is the table overall? Can you do a SELECT DISTINCT into a temp table or CTE, delete everything from the original table, and then repopulate it from that temp/cte table?
If you can't alter the process which imports the data, can you alter the constraints on the table such that it rejects duplicate keys?
ASKER
I though about and I actually did the temp table process but I was looking for a better solution if there is one.
By adding the constraint on the table I will introduce errors on the automatic import that it is not handle by us.
By adding the constraint on the table I will introduce errors on the automatic import that it is not handle by us.
Ok, let me put the question in other way.
Is the RecordID column having different values for all those duplicated records?
Is the RecordID column having different values for all those duplicated records?
Alternatively, can you redirect the initial data load into a temp or staging table, do a select distinct to load the distinct records into the main table, then clear the staging table?
ASKER
Yes, Vitor the recordID for all the duplicates are different.
The only other thing I can think of is adding some kind of trigger on the table that checks the record count of records grouped by those key fields and rolls back the last transaction if it finds more than 1... Might be more hassle than it's worth...
Then you can delete all records like this:
DELETE FROM PrinterLog p1
WHERE EXISTS (SELECT 1
FROM PrinterLog p2
WHERE p1.RecordID > p2.RecordID
AND p1.[PrintTime] = p2.[PrintTime]
AND p1.[PrintBy] = p2.[PrintBy]
AND p1.[PrintPages] = p2.[PrintPages]
AND p1.[PrintCopies] = p2.[PrintCopies]
AND p1.[Printer] = p2.[Printer]
AND p1.[DocumentName] = p2.[DocumentName]
AND p1.[Client] = p2.[Client]
AND p1.[PaperSize] = p2.[PaperSize]
AND p1.[Language] = p2.[Language]
AND p1.[Height] = p2.[Height]
AND p1.[Width] = p2.[Width]
AND p1.[Duplex] = p2.[Duplex]
AND p1.[Grayscale] = p2.[Grayscale]
AND p1.[size] = p2.[size])
DELETE FROM PL
FROM PrinterLog PL
INNER JOIN (
SELECT PrintTime, PrintBY, PrintPages, PrintCopies, Printer, DocumentName, Client, PaperSize, Language, Duplex, Grayscale, size, MAX(RecordID) MAX_RecordID
FROM PrinterLog
GROUP BY PrintTime, PrintBY, PrintPages, PrintCopies, Printer, DocumentName, Client, PaperSize, Language, Duplex, Grayscale, size
HAVING COUNT(*) > 1
) dupl ON PL.RecordID <> PL.MAX_RecordID
ASKER
Vitor when I try to execute your suggestion I get this error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'p1'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'p1'.
I generally like to do import data manipulation outside of the primary table, when possible. It keeps the number of records I'm working with smaller and helps prevent issues like accidentally modifying past records in the primary table. For example, if this table continues to grow, doing a large join or select against the primary table might start getting slow. However, I also know it isn't always feasible to modify the database you're working with, in which case I'd roll with what Vitor or Mate suggested.
ASKER
Mate your suggestion also, has an error:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'MAX_RecordID'.
If I execute the inner Select runs fine but I guess Max_RecordID doesn't get recognized as a field
Msg 207, Level 16, State 1, Line 8
Invalid column name 'MAX_RecordID'.
If I execute the inner Select runs fine but I guess Max_RecordID doesn't get recognized as a field
Try this version in a test environment first or perform a backup of the table before running the command:
DELETE FROM PrinterLog
WHERE EXISTS (SELECT 1
FROM PrinterLog p2
WHERE PrinterLog.RecordID > p2.RecordID
AND PrinterLog.[PrintTime] = p2.[PrintTime]
AND PrinterLog.[PrintBy] = p2.[PrintBy]
AND PrinterLog.[PrintPages] = p2.[PrintPages]
AND PrinterLog.[PrintCopies] = p2.[PrintCopies]
AND PrinterLog.[Printer] = p2.[Printer]
AND PrinterLog.[DocumentName] = p2.[DocumentName]
AND PrinterLog.[Client] = p2.[Client]
AND PrinterLog.[PaperSize] = p2.[PaperSize]
AND PrinterLog.[Language] = p2.[Language]
AND PrinterLog.[Height] = p2.[Height]
AND PrinterLog.[Width] = p2.[Width]
AND PrinterLog.[Duplex] = p2.[Duplex]
AND PrinterLog.[Grayscale] = p2.[Grayscale]
AND PrinterLog.[size] = p2.[size])
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS 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.
Sorry: dupl.MAX_RecordID instead of PL.MAX_RecordID
The comment by @Scott Pletcher is the way to do this. In your case you may just be able to use the PrintTime since you say that it cannot be duplicated. So just have PrintTime in the WITH but that is the way you delete duplicates in a table.
Quite true -- please adjust the PARTITION BY part to your own needs; it's designed to be easy to match your specific requirements for dup checking.
And naturally change the ORDER BY if needed as well. For example, I assumed you'd want to keep the first of the dups, but if you wanted, say, the last one, change to "ORDER BY RecordID DESC".
And naturally change the ORDER BY if needed as well. For example, I assumed you'd want to keep the first of the dups, but if you wanted, say, the last one, change to "ORDER BY RecordID DESC".
COHFL, is this issue solved?
If affirmative, please close this question by accepting the comment or comments that helped you out.
Otherwise let us know what's not working so we can continue helping you.
Cheers
If affirmative, please close this question by accepting the comment or comments that helped you out.
Otherwise let us know what's not working so we can continue helping you.
Cheers
I think the general agreement in the q was that my modern "ROW_NUMBER()" approach was the proper method to use for this task.
I suggest the points be split as follows:
Vitor = 500 pts
Scott = 300 pts
Máté = 200 pts
I suggest the points be split as follows:
Vitor = 500 pts
Scott = 300 pts
Máté = 200 pts
Scotts answer is the appropriate way to do this without trickery. He should get the points awarded to him.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
