September 3, 2008 at 10:06 am
Hi,
I hope someone can help me out here? I have an Id that contains duplicates. I'm trying to only create a new sequence number when the Id changes. Here is an example which might describe it better:
Id
221775
221776
221777
221777
221777
221778
I want my new table to contain a new Seq Number only when the Id changes. Example of results after:
Id Seq_Num
221775 000001
221776 000002
221777 000003
221777 000003
221777 000003
221778 000004
See how the Seq_Num stays the same only until the Id changes? Can the Row_Number() function do something like this or do I have to create a procedure to do this? Help would be appreciated?
Thanks
September 3, 2008 at 11:29 am
In what context are you trying to do this? A SELECT? A View? An INSERT Trigger? A stored procedure that is writing to the table?
It is also not clear to me why you cannot just use the already existing ID number as your Seq_Num?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:35 am
I will be using a strored procedure to take the results and load them to another table using SSIS. They want the seq_num to start at 1 and go up by 1. The Id's currently have gaps in them so we can not use them to our advantage.
Thanks
September 3, 2008 at 11:44 am
Curious, what is the advantage of not having any gaps in the sequence numbers?
😎
September 3, 2008 at 11:50 am
A User request. We probably could have gaps because the Seq_Num will be used as a key but they wanted it to start at 1 and go up by 1.
September 3, 2008 at 11:53 am
Okay, but that isn't an advantage. As for using it as a key, by itself, it isn't because it will duplicate with the id it is assigned. What else would you use with it to make it unique?
😎
September 3, 2008 at 11:55 am
You want the DENSE_RANK() function:
Select Id, DENSE_RANK() OVER(order by Id) as [Seq_Num]
From MyTable
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 11:57 am
Also, is it possible that data may be deleted that may result in sequence numbers changing? Such as id 221777 in your sample data being deleted. Would 221778 still have the same sequence number assigned as when 221777 was in the set?
😎
September 3, 2008 at 12:14 pm
rbarryyoung (9/3/2008)
You want the DENSE_RANK() function:
Select Id, DENSE_RANK() OVER(order by Id) as [Seq_Num]
From MyTable
Geez, get side tracked by work for a couple of minutes and someone beats me to it :w00t: ... So I won't post duplicate code.
😎
September 3, 2008 at 12:19 pm
Works like a charm...exactly what I want. Thanks for all your help!!!!
September 3, 2008 at 1:25 pm
Glad we could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply