CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
Muhammad
Muhammad

Reputation: 129

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

Upvotes: 0

Views: 50

Answers (1)

GMB
GMB

Reputation: 222492

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.

Upvotes: 2

Related Questions