Custom Sequence Values

Question

Custom Sequence Values

dale_keller

Say Hey Kid

Points: 713
More actions
September 3, 2008 at 10:06 am

#197265

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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]

dale_keller Say Hey Kid Points: 713 More actions · Answer 2

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Curious, what is the advantage of not having any gaps in the sequence numbers?

😎

dale_keller Say Hey Kid Points: 713 More actions · Answer 4

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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?

😎

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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?

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.

😎

dale_keller Say Hey Kid Points: 713 More actions · Answer 9

Works like a charm...exactly what I want. Thanks for all your help!!!!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]