I would like to give each player a rank / position based on the values of other columns, 1 being the best position. Each prize or penalty is worth a different amount. Can this be done in SQL and how would I go about putting this into a query? Here is the ratio that i had in mind:

((gold * 8) (silver * 4) (bronze * 2)) - ((disq * 4) penalty) = points

The 'points' are insignificant, ideally I would be able to update the query if the ratio changes.

CodePudding user response：

you can try this.

with table_1 (ID,Name,gold,silver,bronze,disq,penalty)
as
(
  Select           '1','ben',   '1','4'  ,'8','0','5'
  union all Select '2','kim',   '4','1'  ,'3','1','2'
  union all Select '3','sarah', '2','2'  ,'1','0','0'
  union all Select '4','matt',  '0','10' ,'2','1','3'
  union all Select '5','jane',  '2','3'  ,'5','0','1'
)
Select 
    *,
    RANK() Over (order by x.points desc) as rank
from (
    Select 
        *,
        ((gold * 8)   (silver * 4)   (bronze * 2)) -((disq * 4)   penalty) as points
    from 
        table_1
    ) x

CodePudding user response：

Not entirely clear but based on ,

I would like to give each player a rank / position based on the values of other columns, 1 being the best position

You could use the rank window function as follow:

select id, 
        rnk as `rank`, 
        rank()over( order by rnk desc) as new_rank
from ( select id,
              name,
              ((coalesce(gold,0) * 8)   (coalesce(silver,0) * 4)   (coalesce(bronze,0) * 2)) - ((coalesce(disq,0) * 4)   coalesce(penalty,0)) as rnk
       from test 
     ) as tbl;

https://dbfiddle.uk/3FiKkmVA

Note , you need to use coalesce for the null values