Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Need to mark duplicate rows

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
Wonderwall
Asked:
Wonderwall
1 Solution
 
cyberkiwiCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now