0
CREATE TABLE `players` (
 `pid` int(2) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
`score` int(2) NOT NULL,
`game` varchar(20) NOT NULL,
PRIMARY KEY (`pid`),
UNIQUE KEY `pid` (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


INSERT INTO `players` (`pid`, `name`, `score`, `game`) VALUES
(1, 'Samual', 25, 'aa'),
(2, 'Vino', 20, 'aa'),
(3, 'John', 20, 'aa'),
(4, 'Samual', 22, 'bb'),
(5, 'Samual', 21, 'aa'),
(6, 'Vino', 24, 'aa'),
(7, 'John', 25, 'aa'),
(8, 'Vino', 26, 'cc'),
(9, 'John', 23, 'cc'),
(10, 'John', 19, 'aa'),
(11, 'Vino', 20, 'aa'),
(12, 'Samual', 20, 'aa');

In the above table, i want this query to fetch the ranking of a player in descending order base on the sum of the scores gained by that player in a specific game played in the match.

SELECT pid, name, SUM(score) as score, game, rank
FROM (
SELECT pid, name, score, game,
@curRank := IF(@prevRank = score, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := score
FROM player p, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
WHERE game='aa'
ORDER BY score DESC
) s WHERE name ='John'

but the query is not outputting the result in a supposed format, i want the query to sum up all the players scores in a particular game and give the rank of that player and also considering the tie situations

thank you.

Amacyber
  • 1
  • 1
  • Readers answering this question may first wish to check the OP's ranking SQL questions: [one](http://stackoverflow.com/q/29388723), [two](http://stackoverflow.com/q/29391651), [three](http://stackoverflow.com/q/29451275), [four](http://stackoverflow.com/q/29452337) and [five](http://stackoverflow.com/q/29486804), so that work is not accidentally duplicated. – halfer Apr 07 '15 at 08:48

2 Answers2

1

You need to do the aggregation in a subquery and then use the variables to get the rank:

select pid, name, game, score, (@rn := @rn + 1) as rank
from (select pid, name, game, SUM(score) as score
      from player
      where game = 'aa'
      group by pid, game
     ) p cross join
     (select @rn := 0) vars
order by score desc;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you @Gordon Linoff, but the query is still not giving me what i want – Amacyber Apr 05 '15 at 06:29
  • @Amacyber: rather than asking a fifth question on the topic, can you be more specific **in the question** about what you want? – halfer Apr 07 '15 at 08:44
0

You want to use a group by statement rather than a subselect to solve this particular type of problem.

DaOgre
  • 2,080
  • 16
  • 25
  • Please @DaOgre help me with the right query to solve this issue, and i want the query to consider ties. thank you – Amacyber Apr 05 '15 at 06:28