3 added 985 characters in body edited Jan 27 '15 at 11:30 tombom 2,42811 gold badge1313 silver badges2222 bronze badges 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 `````` see it working live in this sqlfiddle 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 `````` see it working live in this sqlfiddle 2 added 1178 characters in body edited Jan 26 '15 at 16:14 tombom 2,42811 gold badge1313 silver badges2222 bronze badges Sample data:Sample data: Query:Query: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 :) Sample data: Query: Sample data:Query: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 :) 1 answered Jan 26 '15 at 16:07 tombom 2,42811 gold badge1313 silver badges2222 bronze badges 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 `````` see it working live in an sqlfiddle