Solved

Need to mark duplicate rows

Posted on 2010-08-12
2
423 Views
Last Modified: 2012-05-10
I need to mark rows in a table that have an ID in the file more than once.  If it is in 3 times then mark it 1,2,3 twice 1,2 once just a 1. can't figure out how to break the count on the id number by grouping using the row_number function any ideas?  mahalo Mark  

0
Comment
Question by:Wonderwall
2 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33423543
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33423630
As a select query

select rn=row_number() over (partition by id order by id), *
from tbl

where rn gives you the "mark" you are after

or as an update if you have a column to hold this value

;with tmp as (
select rn=row_number() over (partition by id order by id), *
from tbl
)
update tmp set targetcol = rn
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

821 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