precose6 precose6 - 23 days ago 6
SQL Question

Creating distinct serialno on unique "product numbers"

I have a table that looks like this:

StgID---|---ItemNumber

1 0034
2 0035
3 0036
4 0036
5 0036
6 0058


And I need a way to assign some sort of unique item number for each row where it would look like this:

StgID---|---ItemNumber--|--SerialID

1 0034 1
2 0035 2
3 0036 3
4 0036 3
5 0036 3
6 0058 4

Answer

You can use DENSE_RANK:

SELECT *,
       DENSE_RANK() OVER(ORDER BY ItemNumber) SerialId
FROM dbo.YourTable;