Question 113436 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I update a column based on a calculation of other columns in a table?

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.

id name gold silver bronze disq penalty rank
1 ben 1 4 8 5
2 kim 4 1 3 1 2
3 sarah 2 2 1
4 matt 10 2 1 3
5 jane 2 3 5 1
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Or you could update your table with the rank

    update test t
inner join ( 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
            ) as t2 on t.id=t2.id
set t.`rank`=t2.`new_rank`;

    Result:

     id    name    gold   silver    bronze    disq    penalty rank
 4     matt    null    10         2        1         3    1
 5     jane    2        3         5       null       1    1
 2     kim     4        1         3        1         2    3
 1     ben     1        4         8       null       5    4
 3     sarah   2        2         1      null       null  5

    https://dbfiddle.uk/ckwNhf6y

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search