TheUndecider
asked on
Find and Mark Duplicate Records in SQL table
Hello, I am working in SQL Server 2005. I am trying to mark duplicate records. Let's say I have these records in a table:
John Bobbit
John Wayne
Mark McSteele
John Bobbit
Rosario Jake
Mark McSteele
Maria Mandrake
John Willis
Mark McSteele
John Bobbit
John Wayne
First I would need to find which ones contain duplicates. Then, I'd need to mark them with a number sequence based on the number of duplicates. For example, in this list
John Bobbit,
Mark McSteele, and
John Wayne have duplicates.
John Bobbit has 3, Mark McSteele also has 3, but John Wayne has 2.
I want to update a field in this table so they get marked like this:
1 John Bobbit
1 John Wayne
1 Mark McSteele
2 John Bobbit
1 Rosario Jake
2 Mark McSteele
1 Maria Mandrake
1 John Willis
3 Mark McSteele
3 John Bobbit
2 John Wayne
As you can see there's a 1, 2, and 3 mark for both John Bobbit and Mark McSteele; 1 and 2 for John Wayne; but the rest are marked as 1 because they don't have duplicates.
Any ideas?
Thanks!
John Bobbit
John Wayne
Mark McSteele
John Bobbit
Rosario Jake
Mark McSteele
Maria Mandrake
John Willis
Mark McSteele
John Bobbit
John Wayne
First I would need to find which ones contain duplicates. Then, I'd need to mark them with a number sequence based on the number of duplicates. For example, in this list
John Bobbit,
Mark McSteele, and
John Wayne have duplicates.
John Bobbit has 3, Mark McSteele also has 3, but John Wayne has 2.
I want to update a field in this table so they get marked like this:
1 John Bobbit
1 John Wayne
1 Mark McSteele
2 John Bobbit
1 Rosario Jake
2 Mark McSteele
1 Maria Mandrake
1 John Willis
3 Mark McSteele
3 John Bobbit
2 John Wayne
As you can see there's a 1, 2, and 3 mark for both John Bobbit and Mark McSteele; 1 and 2 for John Wayne; but the rest are marked as 1 because they don't have duplicates.
Any ideas?
Thanks!
try this.
select row_number() over (partition by your_column order by your_column) as seq_num,
your_column
from your_table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your answer. This is exactly what I was looking for.
ASKER