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

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 2

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, ....)