-1

I have a table like this:

name | score
asdf | 50
zxcv | 100
qwer | 80
aaa  | 70
.
.
.

and I could obtain someone's ranking via this query:

SELECT(
SELECT COUNT(*)
FROM leaderboard m2
WHERE m2.score >= m1.score
) as rank,name,score
FROM leaderboard m1
WHERE name='aaa'

and this query would give me something like this:

rank | name | score
  3  | aaa  |  70

The problem is the result I want to see with name='aaa' is like this : 

rank | name | score
  2  | qwer |  80
  3  | aaa  |  70
  4  | asdf |  50

(with aaa's ranking and the around of aaa's result)

How could it be done?

peak
  • 105,803
  • 17
  • 152
  • 177
r35
  • 1
  • 1
  • 2
    Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) – Drew Jan 25 '16 at 17:31
  • Please do research on mysql and ranking. Plenty of Q&A on this site – Drew Jan 25 '16 at 17:32
  • Take a moment to read through the [editing help](http://stackoverflow.com/editing-help) in the help center. Formatting on Stack Overflow is different than other sites. The better your post looks, the easier it will be for users to help you. – Kyll Jan 25 '16 at 17:40

1 Answers1

0

My self-conclusion on this issue :

SELECT( SELECT COUNT(*) FROM leaderboard m2 WHERE m2.score >= m1.score ) as rank,name,score FROM leaderboard m1 WHERE (SELECT ( SELECT COUNT(*) FROM leaderboard m2 WHERE m2.score >= m1.score ) as rank )>=( SELECT( SELECT COUNT(*) FROM leaderboard m2 WHERE m2.score >= m1.score ) as rank FROM leaderboard m1 WHERE name = 'AAA' ) - 5 ORDER BY rank LIMIT 0,10

r35
  • 1
  • 1