SQLTeam.com | Weblogs | Forums

Row number()


#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