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


INSERT INTO `players` (`pid`, `name`, `age`, `team`) VALUES
(1, 'Samual', 25, 'aa'),
(2, 'Vino', 20, 'bb'),
(3, 'John', 20, 'dd'),
(4, 'Andy', 22, 'cc'),
(5, 'Brian', 21, 'dd'),
(6, 'Dew', 24, 'xx'),
(7, 'Kris', 25, 'qq'),
(8, 'William', 26, 'cc'),
(9, 'George', 23, 'nn'),
(10, 'Peter', 19, 'aa'),
(11, 'Tom', 20, 'aa'),
(12, 'Andre', 20, 'aa');

In the above table, I want this query to fetch the ranking of the players in descending order of their scores.

SELECT pid, name, age, team, rank FROM
(SELECT pid, name, age, team,
@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
@incRank := @incRank + 1, 
@prevRank := age
FROM players p, (
SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
) r 
ORDER BY age DESC) s WHERE team='aa'

It gave me the result below:

Name        |     Age    | Rank  
####--------------------------------   
Samual      |     25     | 2    
Tom         |     20     | 8    
Andre       |     20     | 8    
Peter       |     19     | 12

But I want the results to be returned in this manner:

Name        |     Age    | Rank  
####--------------------------------   
Samual      |     25     | 1    
Tom         |     20     | 2    
Andre       |     20     | 2    
Peter       |     19     | 4

Therefore, making the query to give me the rank within a particular group.

ZygD
  • 22,092
  • 39
  • 79
  • 102
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

1 Answers1

1

You need to move the where clause inside the query as

SELECT pid, 
name, 
age, 
team, 
rank 
FROM (
 SELECT pid, name, age, team,
 @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank, 
 @incRank := @incRank + 1, 
 @prevRank := age
 FROM players p, (SELECT @curRank :=0, @prevRank := NULL, @incRank := 1) r
 WHERE team='aa'
 ORDER BY age DESC
) s
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Exceptional results. :) how you are such great database expert? any tips for beginners for mastering sql that way quickly? – Keep Coding Apr 04 '15 at 21:03
  • I am still not an expert, still learning.. and practice practice and more practice gives more knowledge :-) – Abhik Chakraborty Apr 04 '15 at 21:04
  • Well even though your queries are so meaningful and totally scenario based Sir. :) What are very good resources to have these great skills? – Keep Coding Apr 04 '15 at 21:06
  • What `s` for in the end of query? can you please explain? – Keep Coding Apr 04 '15 at 21:08
  • When you do a `select from (select ...)` meaning your outer query is selecting from a derived table and mysql needs an unique alias name to be given. If you do not give an alias name you will get an error as `Every derived table must have its own alias` , so gave an alias name `s` – Abhik Chakraborty Apr 04 '15 at 21:11
  • Thank you @Abhik Chakraborty, it worked perfectly Ok, but i want the query to work in another form, lets assume the age is replace by the score of a player in a particular game, and all the players have to play more than two games. I want the query to return the ranking based on the sum of the scores a particular player has in all the games played within a particular team. thank you – Amacyber Apr 04 '15 at 21:56
  • 1
    @Amacyber If you study what he did the answer should be obvious. The internal subquery could include your criteria (WHERE ...) and SUM(score). You would then change your IF statement appropriately. Study what you've been given and dare I say it, make a test db so you can do this yourself and you'll be able to apply variations. – gview Apr 05 '15 at 18:51