Skip to content
Advertisement

Assign new value to every unique number in SQL Server

I am new to SQL Server and trying to do some operations

Sample data:

Amount | BillID
-------+-------
 500   | 10009
 500   |  1492
 350   | 15892
 222   | 15596
 899   | 20566
 350   |  9566

How can I create a new column that holds a serial number according to the Amount column so the output looks like:

Amount | BillID | unique
-------+--------+-------
 500   |  10009 |   1
 500   |   1492 |   1
 350   |  15892 |   2
 222   |  15596 |   3
 899   |  20566 |   4
 350   |   9566 |   2

Advertisement

Answer

I would recommend dense_rank():

select t.*, dense_rank() over(order by amount) rn
from mytable t

This assigns a unique, incremental number to each amount. The smallest amount gets ranks 1, and the number are assigned incrementally by increasing amount. This is not exactly the output you showed (where there is no apparent logic to order the ranks), but I think that’s the logic you want in essence.

5 People found this is helpful
Advertisement