Ranking in MySQL

Question

Currently I am developing a system to support a competition. In this competition there are 11 judges that give a grade to the participant. This grade is stored in a table per judge. With the following Select statement I retrieve the total score from the MySQL database (and also some inner joins to select other information from other tables).

Now I want to have ranking in this system, where also ex aequo is taken into account. I have tried several mysql solutions from this forum. But somehow the statement is always incorrect. Hopefully, someone can help me.

SELECT optreden.id, optreden.wedstrijd, optreden.jaartal, 
liedje.titel, club.volledige_clubnaam,
(SELECT SUM(score.score_lied) FROM score WHERE score.optreden=optreden.id) AS score
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC

With this query, I get the following result:

+----+--------------+------+----------+--------------+------+
| id | Competition  | Year | Title    | Artist (club)| Score|
+----+--------------+------+----------+--------------+------+
| 12 | voorselectie | 2014 | Song 1   | Club 1       | 792  |
| 16 | voorselectie | 2014 | Song 2   | Club 2       | 600  |
| 10 | voorselectie | 2014 | Song 3   | Club 3       | 600  |
| 11 | voorselectie | 2014 | Song 3   | Club 3       | 500  |
| 12 | voorselectie | 2014 | Song 3   | Club 3       | 400  |

The query fetches information from the following tables:

Score: in this table the scores given by the 11 judges is stored as line-items (result is given below);
Optreden: in this table the information from the club and the song is linked;
Club: information from the club, such as name;
Liedje: information about the song, performed by the club. All the tables are linked by id's. In the result, there should come a column, with the ranking of the club.

The result from the table score is as follows:

+----+----+--------+----+
| ID | Pid| Userid | 85 |
+----+----+--------+----+
| 1  | 12 | 444    | 85 |
| 12 | 12 | 454    | 92 |
| 13 | 12 | 445    | 87 |
| 14 | 12 | 446    | 56 |
| 15 | 12 | 447    | 81 |
| 16 | 12 | 448    | 78 |
| 17 | 12 | 449    | 55 |
| 18 | 12 | 450    | 69 |
| 19 | 12 | 451    | 88 |
| 20 | 12 | 452    | 69 |
| 21 | 12 | 453    | 32 |
+----+----+--------+----+

Where the columns represent the following: - ID: the id of the score in the table; - Pid: the id of the performance (with this performing club and song are linked); - Userid: the userid of the 11 judges; - Score: the score given by each judge.

The end result should be as follows:

+----+--------------+------+----------+--------------+------+------+
| id | Competition  | Year | Title    | Artist (club)| Score| Rank |
+----+--------------+------+----------+--------------+------+------+
| 12 | voorselectie | 2014 | Song 1   | Club 1       | 792  | 1    |
| 16 | voorselectie | 2014 | Song 2   | Club 2       | 600  | 2    |
| 10 | voorselectie | 2014 | Song 3   | Club 3       | 600  | 2    |
| 11 | voorselectie | 2014 | Song 3   | Club 3       | 500  | 4    |
| 12 | voorselectie | 2014 | Song 3   | Club 3       | 400  | 5    |

I have tried the solution from Neville before and now again. I have rewritten the SQL statement as follows:

SELECT optreden.id, optreden.wedstrijd, optreden.jaartal, 
liedje.titel, club.volledige_clubnaam,
(SELECT SUM(score.score_lied) FROM score WHERE score.optreden=optreden.id) AS score, CASE
WHEN @prev_value = score THEN @rank_count
WHEN @prev_value := score THEN @rank_count := @rank_count + 1
END AS rank,
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC

Here I get the error: Unknown column 'score' in 'field list'..

I agree. Plus some context around the data sructure would no be amiss. — Shadow, Oct 05 '15 at 09:00
Do you really need a table for each judge? That sounds like massive over normalization. — e4c5, Oct 05 '15 at 09:01
I have added some more information. I have not saved the queries tried, therefor I can't give you the errors. — Nico Prevoo, Oct 05 '15 at 09:30
Sorry, added that too.... that's the problem with hours of coding, the problem is clear for yourself... :D — Nico Prevoo, Oct 05 '15 at 09:33
So, in PHP, just spit the results out in a loop. While the scores are different, increment the rank. — Strawberry, Oct 05 '15 at 09:34
There's an answer here: http://stackoverflow.com/questions/3333665/rank-function-in-mysql — Neville Kuyt, Oct 05 '15 at 10:23
Neville, I tried this before. However, I got a failure. I have edited my original post and posted the error. — Nico Prevoo, Oct 05 '15 at 12:05

score 0 · Answer 1 · answered Oct 05 '15 at 12:29

It seems that you are getting this error simply because there is no field score... I have extracted your inner SELECT into a JOIN so the real field can be used directly. (Not tested though, sorry)

SELECT optreden.id, optreden.wedstrijd, optreden.jaartal, 
liedje.titel, club.volledige_clubnaam,
SUM(score.score_lied) AS score, CASE
WHEN @prev_value = SUM(score.score_lied) THEN @rank_count
WHEN @prev_value := SUM(score.score_lied) THEN @rank_count := @rank_count + 1
END AS rank,
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
LEFT JOIN score ON score.optreden=optreden.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC

Please note that you might face another little problem with this kind of ranking: Normally, when two entities end up on the same rank, the next rank is not given to the next person. Here's what I mean with an example, with two persons ranking in 3:

1, 2, 3, 3, 5

But in your code, it will give:

1, 2, 3, 3, 4

I hope this helps.

score 0 · Answer 2 · answered Oct 06 '15 at 10:19

I have tried several things. However, I had forgotten to tell you one important feature I was gonna use. The ranking of the clubs should be presented through a presentation on a big screen. Therefor the MySQL code changed a little.

I have added a support table to my database. In this support table the sum of the scores is recorded with a INSERT INTO...SELECT statement.

When this insert is done. An update script is launched which makes the ranking in the table and also makes a correct calculation for ex aequo.

Here's the update statement:

SET @rank=0, @last_score = null, @tie_build_up = 0;
UPDATE ranking_voorselectie_lied
SET rank= @rank:= if(@last_score = totaal_score_lied, @rank, @rank+@tie_build_up+1),
tie_build_up= @tie_build_up:= if(@last_score = totaal_score_lied, @tie_build_up+1, 0),
last_score= @last_score:= totaal_score_lied
WHERE wedstrijd="voorselectie" AND jaar=2014
ORDER BY totaal_score_lied DESC;

This gives me the following result:

        +--------------+------+----------+-------------------+------+-----+------------+
|  Wedstrijd   | Jaar | Optreden | totaal_score_lied | Rank | Tie | last_score |
+--------------+------+----------+-------------------+------+-----+------------+
| voorselectie | 2014 |       12 |               792 |    1 |   0 |        792 |
| voorselectie | 2014 |       16 |                82 |    2 |   0 |         82 |
| voorselectie | 2014 |       10 |                73 |    3 |   0 |         73 |
| voorselectie | 2014 |       15 |                51 |    4 |   0 |         51 |
| voorselectie | 2014 |        3 |                50 |    5 |   0 |         50 |
| voorselectie | 2014 |       11 |                42 |    6 |   0 |         42 |
| voorselectie | 2014 |       13 |                38 |    7 |   0 |         38 |
| voorselectie | 2014 |        8 |                38 |    7 |   1 |         38 |
| voorselectie | 2014 |       14 |                37 |    9 |   0 |         37 |
| voorselectie | 2014 |        5 |                35 |   10 |   0 |         35 |
| voorselectie | 2014 |        4 |                33 |   11 |   0 |         33 |
| voorselectie | 2014 |        7 |                25 |   12 |   0 |         25 |
| voorselectie | 2014 |        9 |                23 |   13 |   0 |         23 |
+--------------+------+----------+-------------------+------+-----+------------+

Ranking in MySQL

2 Answers2