July 10, 2008 at 8:01 am
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
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
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
Thanks ever so much. That is exactly what I want.
July 10, 2008 at 9:49 am
Rather difficult to assist you considering the minimum amount of information you have supplied.
Please read
http://www.sqlservercentral.com/articles/SQLServerCentral.com/62764/ and then reply here with the essential information so that you can receive meaningful help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply