# Selecting, ranking and applying a point score based on rank

I am writing an MVP system based on votes from users. The table will look like

```vote     | game | year
--------- ------ ------
player 1 | 2    | 2015
player 1 | 2    | 2015
player 2 | 2    | 2015
player 2 | 2    | 2015
player 2 | 2    | 2015
player 3 | 2    | 2015
player 1 | 2    | 2015
player 3 | 2    | 2015
player 4 | 2    | 2015

```

An entry here equates to a vote. So out of this we would group and get a count of

```Player 1 = 3
Player 2 = 3
Player 3 = 2
Player 4 = 1
```

The MVP system is a "3-2-1" score system. So points are awarded to the players in reverse order. So it should only apply winning scores to the top 3 positions. i.e.

```1st gets 3 points
2nd gets 2 points
3rd gets 1 point
```

This gets really complicated when there is a tie but top 3 should get MVP points.

In the case above the point spread would be

Player 1 = 2.5 points Player 2 = 2.5 points Player 3 = 1 point

This is worked out because the top 2 players are effectively 1st and 2nd, there is no second so the next person gets 1 point for placing 3rd.

Effectively (points = points_available / players_in_rank)

however that equation is wrong. It gets extra complicated if we have a tie for 3rd, where you could possibly end up with lots of people in 3rd spot and therefore they get 1 point divided by them.

It is really complicated when you have ties in 1st or second. I have worked around this poorly in PHP code but would like to be able to do this in SQL and do away with PHP.

Just wondering if anyone has any simple methods for this?

These scores points are then later added to an overall standings table

Regards Dan

## 1 Answer

Sample data:

``````CREATE TABLE v
(`vote` varchar(8), `game` int, `year` int)
;

INSERT INTO v
(`vote`, `game`, `year`)
VALUES
('player 1', 2, 2015),
('player 1', 2, 2015),
('player 2', 2, 2015),
('player 2', 2, 2015),
('player 2', 2, 2015),
('player 3', 2, 2015),
('player 1', 2, 2015),
('player 3', 2, 2015),
('player 4', 2, 2015)
;
``````

Query:

``````SELECT
vote,
no_votes,
rank,
3 - rank + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points
FROM (

SELECT
sq.*
, @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
, @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
, @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
, @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
, @prevNoVotes := no_votes
FROM
(

SELECT
vote,
COUNT(*) AS no_votes
FROM v
GROUP BY vote

) sq
, (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
ORDER BY no_votes DESC

) q
``````

Result:

``````|     VOTE | NO_VOTES | RANK | POINTS |
|----------|----------|------|--------|
| player 1 |        3 |    1 |    2.5 |
| player 2 |        3 |    1 |    2.5 |
| player 3 |        2 |    2 |      2 |
| player 4 |        1 |    3 |      1 |
``````

Explanation:

This

``````        SELECT
vote,
COUNT(*) AS no_votes
FROM v
GROUP BY vote
``````

is simple enough, I hope :)

This

``````, (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
``````

is just a subquery to initialize some variables.

Here

``````    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
, @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
, @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
, @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
, @prevNoVotes := no_votes
``````

in the first row `@prevNoVotes` holds the value of the previous row (or the value from the initialization subquery). `@rc1`, `@rc2` and `@rc3` are just rank counters. They count for each rank, how many people are on the same rank.
In the last row, the value of the current row is assigned to the variable. When the next row is processed, the variable will hold the value of the previous row in the first line of this part of the code.

And finally in the most outer part of the query we calculate the points distribution.

Feel free to ask any questions :)

UPDATE:

Added this table:

``````CREATE TABLE points
(`r` int, `p` int)
;

INSERT INTO points
(`r`, `p`)
VALUES
(1, 3),
(2, 2),
(3, 1)
;
``````

Added more logic :)

``````SELECT
vote,
no_votes,
rank,
@rc := @rc + 1 AS rowcounter,
((SELECT p FROM points WHERE r = IF(rank = @prevRank, rank, @rc)) - 1) + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points,
@prevRank := rank
FROM (
SELECT
sq.*
, @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
, @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
, @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
, @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
, @prevNoVotes := no_votes
FROM
(

SELECT
vote,
COUNT(*) AS no_votes
FROM v
GROUP BY vote

) sq
, (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
ORDER BY no_votes DESC
)q
, (SELECT @rc := 0, @prevRank := 1) row_counter_init
ORDER BY rank
``````
• This isn't quite right as it gives scores to player 4. The score should be `player 1 & 2 = 2.5 player 3 = 1 Player 4 = 0` Basically the split removes the scores after it until it gets to 0 so 2 people in position 1 would split the amount for first and second, 3 people in rank one would split the entire amount amongst them and so on – Dannymh Jan 26 '15 at 22:37
• additionally would it possible to get the points for 1st 2nd and 3rd from another table? say 1st place - select point from mvpsetting where pos=1 and still do the calculations? – Dannymh Jan 26 '15 at 23:21
• just tagging to make sure @tombom – Dannymh Jan 26 '15 at 23:37
• @Dannymh Updated my answer. Please have a try. – tombom Jan 27 '15 at 11:30
• amazing, so elegant and so amazing, this is exactly what I needed. Thank you a thousand times – Dannymh Jan 27 '15 at 11:48