We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

Remove duplicates on a Table

COHFL
COHFL asked
on
90 Views
Last Modified: 2018-03-07
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:
SELECT DISTINCT [RecordID]
      ,[PrintTime]
      ,[PrintBy]
      ,[PrintPages]
      ,[PrintCopies]
      ,[Printer]
      ,[DocumentName]
      ,[Client]
      ,[PaperSize]
      ,[Language]
      ,[Height]
      ,[Width]
      ,[Duplex]
      ,[Grayscale]
      ,[size]
  FROM [[PrinterLog]

Open in new window


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!
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
What defines a duplicate record? i.e. which columns should be compared so we know that we're facing  a duplicate record?

Author

Commented:
here is a sample of the data.
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                                             

Open in new window

Author

Commented:
notice how all those records have the same PrintTime, PrintBy, ...
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Good. Which one of those should be preserved?

Author

Commented:
only one row entry per duplicate
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?

Author

Commented:
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:
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
...

Open in new window



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 

Open in new window

Jeff EdmundsApplication Developer/SQL DBA

Commented:
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?
Jeff EdmundsApplication Developer/SQL DBA

Commented:
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?

Author

Commented:
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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Ok, let me put the question in other way.
Is the RecordID column having different values for all those duplicated records?
Jeff EdmundsApplication Developer/SQL DBA

Commented:
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?

Author

Commented:
Yes, Vitor the recordID for all the duplicates are different.
Jeff EdmundsApplication Developer/SQL DBA

Commented:
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...
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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])

Open in new window

NOTE: The p1.RecordID > p2.RecordID filter assures to let one record after deletion. The one with a lower RecordID.
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
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

Open in new window

Author

Commented:
Vitor when I try to execute your suggestion I get this error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'p1'.
Jeff EdmundsApplication Developer/SQL DBA

Commented:
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.

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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])

Open in new window

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
Sorry: dupl.MAX_RecordID instead of PL.MAX_RecordID
DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
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.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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".
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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
DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
Scotts answer is the appropriate way to do this without trickery.  He should get the points awarded to him.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.