Row number()

Transact-SQL
1

I'm trying to create a unique invoice number for records that have the same reference.

I'm trying to look at the Ref field and generate the Inv column below.

Ref Inv
ABC 1
ABC 1
ABC 1
HIJ 2
HIJ 2
BA 3
BA 3

2

So you want
ABC 1
ABC 2
ABC 3
????

What have you tried? Do you have a problem with ROW_NUMBER()?

3

There's probably a better way, but here you go:

WITH UniqInv (Ref, Inv)
AS
(SELECT Ref, Inv = ROW_NUMBER() OVER (ORDER BY Ref)
FROM @t
GROUP BY Ref)
SELECT t.Ref, UniqInv.Inv
FROM @t t
JOIN UniqInv ON t.Ref = UniqInv.Ref

4

Oh this is easier!

SELECT Ref, Inv = DENSE_RANK() OVER(ORDER BY Ref)
FROM @t