I have a table with the following properties

I want to write an SQL script creating 3 additional columns, ranking "acc gained", "Job gained" and "Rev gained", with the highest value Being rank 1.

However, I do not want draws in any category. If 2 people have the same amount of "acc gained", refer to the highest value based on Jobs gained, and if Jobs gained are the same, refer to "rev gained". it is very unlucky that 2 people would have the exact same rev gained".

Logic as follow:

Acc Rank: check "Acc gained", if same check "Jobs Gained", if same check "rev gained"

Jobs Rank: check "jobs gained", if same check "rev gained"

Rev gained: check "rev gained" (never had a situation where 2 ppl have the same rev)

sample desired output:

CodePudding user response：

This is just an ordering of columns for which you can use row_number:

select *, 
    Row_Number() over(order by Acc_Gained desc,Jobs_Gained desc, Rev_Gained desc) Acc_Rank,
    Row_Number() over(order by Jobs_Gained desc, Rev_Gained desc) Job_Rank,
    Row_Number() over(order by Rev_Gained desc) Rev_Rank
from t;

Example Fiddle