Assign Unique ID within groups of records

Question

I have a situation where I need to add an arbitrary unique id to each of a group of records. It's easier to visualize this below.

Edited 11:26 est: Currently the lineNum field has garbage. This is running on sql server 2000. The sample that follows is what the results should look like but the actual values aren't important, the numbers could anything as long as the two combined fields can be used for a unique key.

OrderID      lineNum
AAA          1
AAA          2
AAA          3
BBB          1
CCC          1
CCC          2

The value of line num is not important, but the field is only 4 characters. This needs to by done in a sql server stored procedure. I have no problem doing it programatically.

Answer 1

You could create a cursor that reads all values sorted, then at each change in value resets the 1 then steps through incrementing each time.

E.g.:

AAA reset 1
AAA set 1 + 1 = 2
AAA set 2 + 1 = 3
BBB reset 1
CCC reset 1
CCC set 1 + 1 = 1

Answer 2

Hmmmmm, could you create a view that returns the line number information in order and group it based on your order ID? Making sure the line number is always returned in the same order.

Either that or you could use a trigger and on the insert calculate the max id for the order?

Or perhaps you could use a select from max statement on the insert?

Perhaps none of these are satisfactory?

Answer 3

While adding a record to the table, you could create the "linenum" field dynamically:

In Transact-SQL, something like this:

Declare @lineNum AS INT

-- Get next linenum
SELECT @lineNum = MAX(COALESCE(linenum, 0)) FROM Orders WHERE OrderID = @OrderID
SET @lineNum = @lineNum + 1

INSERT INTO ORDERS (OrderID, linenum, .....)
VALUES (@OrderID, @lineNum, ....)

Answer 4

Assuming your using SQL Server 2005 or better you can use Row_Number()

select orderId,
       row_number() over(PARTITION BY orderId ORDER BY orderId) as lineNum
from Order

Answer 5

If you're not using SQL 2005 this is a slightly more set based way to do this (I don't like temp tables much but I like cursors less):

declare @out table (id tinyint identity(1,1), orderid char(4))

insert @out select orderid from THESOURCETABLE

select 
    o.orderid, o.id - omin.minid + 1 as linenum
from @out o
    inner join
     (select orderid, min(id) minid  from @out group by orderid) as omin on
      o.orderid = omin.orderid