0

I'm new at programming with PHP/mySQL and I have got a little problem here.

This is how my code looks like:

SELECT  
 COUNT(*) AS Number , Name 
FROM 
 table 
GROUP BY 
 Name 
ORDER BY 
 Number DESC 
LIMIT 100

Out of this table:

Oliver
Michael
Oliver
Steven
Michael
Oliver
Oliver

... it generates:

Oliver  | 4
Michael | 2
Steven  | 1

My question: How can I add ranking numbers so that it looks like this:

1 | Oliver  | 4
2 | Michael | 2
3 | Steven  | 1

Thanks for your help!

1
  • 1
    Questions :Q1) Suppose Oliver and Michael have 4. Would the ranking be : 1 Oliver 4, 1 Michael 4, 3 Steven 1 or 1 Oliver 4, 2 Michael 4, 3 Steven 1 ? Q2) Suppose Michael and Steven had 2. Would the ranking be : 1 Oliver 4, 2 Michael 2, 3 Steven 2 or 1 Oliver 4, 2 Michael 2, 2 Steven 1 ? Commented Jan 24, 2016 at 16:51

1 Answer 1

1
SELECT  state, num_cities, @n := @n+1 AS rank
    FROM  
      ( SELECT  state, count(*) AS num_cities
            FROM  us
            GROUP BY  state
            ORDER BY  num_cities DESC 
      ) AS x
    JOIN  
      ( SELECT  @n:=0 ) AS init

+-------+------------+------+
| state | num_cities | rank |
+-------+------------+------+
| CA    |        447 |    1 |
| FL    |        264 |    2 |
| TX    |        238 |    3 |
| IL    |        221 |    4 |
| NY    |        185 |    5 |
| OH    |        185 |    6 |
| MA    |        180 |    7 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.