Custom Sequence Values

  • 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

  • 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]

  • 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

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

    😎

  • 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.

  • 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?

    😎

  • 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]

  • 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?

    😎

  • 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.

    😎

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

  • 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