CODEWITHSUNDEEP
sqlsql-server
Ankit Vora
Ankit Vora

Reputation: 702

Assign Rank to each row based on the type of row SQL

My Table

DiagnosisCodes

Sequence  Type        Value
 1         Diagnosis   123
 22        Diagnosis   456
 38        Principal   111
 4         Diagnosis   789
 53        Principal   222

I need to select from the Diagnosis table and assign a rank to each row based on the Sequence and Type. Lowest Sequence gets the lowest rank for that Type.For example: Sequence 1 is the lowest sequence for Type "Diagnosis" so its rank should be 1 similarly Sequence 4 is second lowest for type "Diagnosis" so its rank should be 2. Sequence 38 is lowest for Type "Principal" so should be ranked 1 and so on.

Output I expect:

Sequence  Type        Value Rank
 1         Diagnosis   123  1
 22        Diagnosis   456  3
 38        Principal   111  1
 4         Diagnosis   789  2
 53        Principal   222  2

I was thinking of using pivot or partition but not sure if that's the best way to do it.

Upvotes: 1

Views: 43

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93744

Use Row_Number Window Function to create the rank

select sequence , Type, Value,
       row_number() over(partition by Type order by sequence) As [Rank] 
From yourtable

Note : In case of tie if you need same rank then use Dense_Rank instead of Row_number

Upvotes: 3

Related Questions