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
    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 ? – RolandoMySQLDBA Jan 24 '16 at 16:51
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, privacy policy and cookie policy

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