1

I am trying to get a score ranking with ties - but without skipping ranks 1. Thomas 2. Peter 2. Jaden 4. Maria

The first version of our DB query was simple:

"SELECT * FROM $Table WHERE Score > 0 ORDER BY Score LIMIT $Count OFFSET $Offset"

Then I got a1ex07 version to work:

"SELECT UID, Name, Score, Rank, (SELECT COUNT(*) FROM $Table t2 WHERE t2.Score < t1.Score AND Score > 0) +1 AS Rank FROM $Table t1 WHERE Score > 0 ORDER BY Rank LIMIT $Count OFFSET $Offset"

But there are the spaces...

My most relevant results:

  1. MySQL Rank with ties Solutions worked but skipped ranks (1 -> 1 -> 3 -> 4...)
  2. MySQL Rank in the Case of Ties Solution worked but skipped ranks again
  3. Simple MySQL Update Rank with Ties rank skipped again...
  4. How do I Handle Ties When Ranking Results in MySQL? Not very related...

I hope someone can help me;

MySQL(i) 10.1.10-MariaDB

Community
  • 1
  • 1
Rhutos
  • 11
  • 3
  • I remember I have read a question about the same issue here on so before. It didn't have the same wording as your but i guess you can find it looking for ranking. It was solved easily with all the same goal as your – Lelio Faieta Mar 13 '16 at 18:50

1 Answers1

0

To achieve ranking in mysql one can use session variables:

SELECT  tmp.UID,
        tmp.Name,
        @rnk := IF(@score = tmp.Score, @rnk, @rnk+1) AS Rank,
        @score := tmp.Score as Score
FROM    (
            SELECT      *
                FROM    $Table,
                        (SELECT @rnk := 0, @score = NULL) AS init
            ORDER BY    Score DESC
        ) AS tmp

The subquery sorts the data by the score and initializes variables. The primary query compares the score of the current row with the score of the previous one, determining the rank

piotrgajow
  • 2,880
  • 1
  • 22
  • 23
  • Thanks for your help. It works - but It skips ranks like the other code snippets ^^ – Rhutos Mar 13 '16 at 18:38
  • Then what does it mean that it skips ranks? In my test it gives result 1->2->2->3 (http://sqlfiddle.com/#!9/a6591/1) is this not what you are asking? Or does it give different result in your example? If so could you please give some data sample, and mysql version you are using? – piotrgajow Mar 13 '16 at 21:40
  • I've used your command in phpMyAdmin Mysql version: 5.0.11-dev Result: http://upload.rising-games.net/upload/Public/Results.png There must be something wrong on my server... :( – Rhutos Mar 14 '16 at 17:20
  • Ok... I have downloaded oldest version of MySQL available on their website (5.0.15) and the query does not work. It should work on 5.5 and 5.6 (tested it on 5.5.44, 5.6.17, 5.6.11). I would advise to update MySQL on your server because 5.0.11 is realy old. I might think of a workaround if update is not possible for you. – piotrgajow Mar 14 '16 at 19:23
  • Currently I try to update to 5.7.11... But we're using xampp (7.0.4-0 - on debian 8). And it not seems to be that easy... – Rhutos Mar 15 '16 at 13:11
  • I had problems with upgrading MySQL to 5.7 on Windows, and did not try updating on unix systems. If you have any problems I would suggest using 5.6.*. Then again I do not use xampp, just standalone MySQL instance. – piotrgajow Mar 15 '16 at 13:22
  • Sorry, my mistake. Our server is using >MySQL(i) 10.1.10-MariaDB< which is equal to oracle mysql 5.6 / 5.7. So I still don't know the problem... – Rhutos Mar 15 '16 at 17:02
  • I am affraid it is not exactly equal, as in mysql 5.6 it works... Maybe you should update your question informing that it relates to mysqli (as well as change the tag!), and someone else will be able to explain what is going on. – piotrgajow Mar 15 '16 at 19:13