Find the ranking of an integer in mysql

Question

Possible Duplicate:
Mysql rank function

I have the following countryTable

country  clicks
-------  ------
0        222
66       34 
175      1000
45       650

How do I get the ranking of say country 45 which is 2 in this case?

I assume u want that in mysql? do notice mysql is to store data rather than to implement logic. Although, it can be done. But from my experience bottlenecks for the majority of apps is the DB rather than the business logic layer — Itay Moav -Malimovka, Jan 12 '13 at 15:01

score 2 · Answer 1 · answered Jan 12 '13 at 15:01

2

Ordered by country ASC:

SELECT 1+COUNT(*) AS ranking
FROM countryTable
WHERE country < 45 ;

Ordered by clicks DESC:

SELECT 1+COUNT(*) AS ranking
FROM countryTable AS t
  JOIN countryTable AS c
      ON c.clicks > t.clicks
WHERE t.country = 45 ;

answered Jan 12 '13 at 15:01

ypercubeᵀᴹ

113,259
19
174
235

Does not cover cases where there is a tie. – Salman A Jan 12 '13 at 18:40
@SalmanA Why, I think it does. It doesn't cover only the case when there is no country `45`. – ypercubeᵀᴹ Jan 12 '13 at 23:06
http://sqlfiddle.com/#!2/b8975/3: two countries have same clicks. The ranks are 1, 2, 2, 4 (better 1, 2, 3, 4 or 1, 2, 2, 3). OP did not mention if/how ties should be handled though. – Salman A Jan 12 '13 at 23:16
@SalmanA I agree that ties may complicate things (depending how the OP wants to deal with them). My query simulates the `RANK()` function: [Ranking Functions](http://msdn.microsoft.com/en-us/library/ms189798.aspx). The 1, 2, 3, 4 would be `ROW_NUMBER()` and would require clarification for how ties should be handled. The 1, 2, 2, 3 would be `DENSE_RANK()` in standard SQL. – ypercubeᵀᴹ Jan 12 '13 at 23:26
[SQL-Fiddle](http://sqlfiddle.com/#!3/47a6a/2) – ypercubeᵀᴹ Jan 12 '13 at 23:29
+1 for for simulating `rank()` :) – bonCodigo Jan 19 '13 at 22:38

score 2 · Answer 2 · answered Jan 12 '13 at 15:01

2

You can get 2 rank as below it like below:

Select * from tabeName order by clicks limit 1,1

For 3 rank:

Select * from tabeName order by clicks limit 2,1

answered Jan 12 '13 at 15:01

Suresh Kamrushi

15,627
13
75
90

Hey, how come I give the same answer and get downvoted???? :-D damm...people are a herd, not you. your ansdwer is the best ;-) and i'll upvote u – Itay Moav -Malimovka Jan 14 '13 at 15:21

score 1 · Answer 3 · answered Jan 12 '13 at 15:01

1

SELECT *
FROM 
(
  SELECT  @ranking:= @ranking + 1 rank,
          a.country,
          a.clicks
  FROM    tableName a, (SELECT @ranking := 0) b
  ORDER BY a.clicks DESC
) s
WHERE country = 45

SQLFiddle Demo

answered Jan 12 '13 at 15:01

John Woo

258,903
69
498
492

Does not cover cases where there is a tie. One extra variable will help. – Salman A Jan 12 '13 at 18:44

score 1 · Answer 4 · answered Jan 12 '13 at 15:08

1

This will show the correct rank (2) for country 45. You don't specify how to rank ties, so you may want to change the comparison to suit you. Non existing countries rank as 0.

SELECT COUNT(*) rank 
FROM countryTable a
JOIN countryTable b
  ON a.clicks <= b.clicks
WHERE a.country = 45

SQLfiddle here.

answered Jan 12 '13 at 15:08

Joachim Isaksson

176,943
25
281
294

It works. But (somehow unrelated if you don't mind) how do I access the return value in php? I tried $row = mysql_fetch_array($result) or die(mysql_error()); echo $row['rank']; but it returns blank – PutraKg Jan 12 '13 at 16:02
@PutraKg Sounds like the correct approach, not sure what your problem may be. You may want to change to mysqli_* functions since mysql_* functions are deprecated, but both should work in this case. – Joachim Isaksson Jan 12 '13 at 16:13

score 0 · Answer 5 · edited Jan 12 '13 at 15:25

0

X is the rank you need to look for:

SELECT * FROM T ORDER BY clicks DESC LIMIT X-1,1

edited Jan 12 '13 at 15:25

krock

28,904
13
79
85

answered Jan 12 '13 at 15:04

Itay Moav -Malimovka

52,579
61
190
278

score 0 · Answer 6 · edited Jan 12 '13 at 17:08

Here's another (stunningly fast) way (albeit limited to 256 rows):

SELECT country
     , clicks
     , FIND_IN_SET(clicks,(SELECT GROUP_CONCAT(DISTINCT clicks ORDER BY clicks DESC) FROM country_clicks)) rank
FROM country_clicks

or, if you prefer...

SELECT FIND_IN_SET(clicks,(SELECT GROUP_CONCAT(DISTINCT clicks ORDER BY clicks DESC) FROM country_clicks)) rank
   FROM country_clicks
  WHERE country = 45;

Find the ranking of an integer in mysql

6 Answers6

Linked