Post reply

Generating a Custom Sequence Number in SQL Server 2005

  • July 10, 2008 at 8:01 am

    #189383

    Hi,

    I'm trying to figure out how to custom generate sequence numbers in SQL Server 2005. I have a table that contains Id's that at times are duplicated. I want to generate a sequence number starting at 1 and incrementing by 1 everytime the Id is the same but starting at 1 again when a new Id comes along - kind of like a control break. Here is an example:

    Id Seq_NUm

    123 1

    234 1

    234 2

    234 3

    567 1

    567 2

    789 1

    ect....etc

    I have been trying to figure how to do this in SQL code or how it can be done in SSIS - maybe the script component where I check the current Id to the previous Id and if they are the same I increment the Seq_Num and if it changes I reset Seq_Num back to 1.

    Any ideas would be appreciated?

    Thanks

  • July 10, 2008 at 8:30 am

    #840868

    Try this from BOL, also it would help to read up on the uniqueidentifier data type.

    "I. Using the uniqueidentifier data type in a column

    The following example creates a table with a uniqueidentifier column. The example uses a PRIMARY KEY constraint to protect the table against users inserting duplicated values, and it uses the NEWSEQUENTIALID() function in the DEFAULT constraint to provide values for new rows. The ROWGUIDCOL property is applied to the uniqueidentifier column so that it can be referenced using the $ROWGUID keyword.

    CREATE TABLE Globally_Unique_Data

    (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,

    Employee_Name varchar(60)

    CONSTRAINT Guid_PK PRIMARY KEY (Guid) );"

  • July 10, 2008 at 8:55 am

    #840892

    I think you can do it quite simply.

    SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID, ORDER BY ID) AS Seq_Num

    FROM ... etc

    See the ROW_NUMBER() function in BOL

  • July 10, 2008 at 9:32 am

    #840926

    Thanks ever so much. That is exactly what I want.

  • July 10, 2008 at 9:49 am

    #840936

    Rather difficult to assist you considering the minimum amount of information you have supplied.

    Please read

    http://qa.sqlservercentral.com/articles/SQLServerCentral.com/62764/ and then reply here with the essential information so that you can receive meaningful help

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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