CODEWITHSUNDEEP
mysqlsortingstored-proceduressql-order-bymysql-8.0
wotsepotse
wotsepotse

Reputation: 7

Rank values in Mysql

I am having trouble finding a solution. I need to make a column that rank from the most to the least value (column bedrag). If the values are the same then the value with the highest number ( column spelersnr ) 'wins'. I hope you can help me out.

This is what I got so far. 

SELECT    s.spelersnr, 
          naam , 
          (select max(bedrag) from boetes b  where b.spelersnr = s.spelersnr) as mbedrag,
          @curRank := @curRank + 1 AS POSITIE
FROM      spelers s, (SELECT @curRank := 0) r
ORDER BY  mbedrag ;

Upvotes: 0

Views: 28

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522571

Given that you are using MySQL 8+, you may try using the ROW_NUMBER function here. Also, we can rewrite your query using a join, to eliminate the correlated subquery in the select clause:

SELECT s.spelersnr, s.naam,
    ROW_NUMBER() OVER (ORDER BY b.mbedrag DESC, s.spelersnr DESC) rn
FROM spelers s
INNER JOIN
(
    SELECT spelersnr, MAX(bedrag) AS mbedrag
    FROM boetes
    GROUP BY spelersnr
) b
    ON s.spelersnr = b.spelersnr
ORDER BY
    b.mbedrag;

Upvotes: 1

Related Questions