0

I've got this little problem: I want to organize a top10 list for my game using MySQL. Well, actually it already works, but I need to add one feature to it. Say, I got the table which contains a field called 'level' for example, there's 10 thousand players in a database, each of them has his own level. So, to create a top ten list I do this:

SELECT `values`.`level` FROM `values` ORDER BY `values`.`level` DESC LIMIT 10

It works fine and gives me 10 players with the highest level. But what if the player himself is not on top10 list? And he wants to know how much is he left to pass to get there. Let's represent it like this: he sees the score table showing 10 best players, and at the bottoms of the table sees his own record saying, e.g your position is 4023 Of course I can retreive all records from MySQL order them by level descending, and count users's position using a loop, but captain obvious is telling me it isn't the most rational approach :-)

I need the result look like this (only for 10 users, not 5)

    +-------+------+--------+
    | level | pos  |user    |
    +-------+------+--------+
    | 23    |    1 |Alex    |
    | 19    |    2 |John    |
    | 18    |    3 |James   |
    | 16    |    4 |Ashley  |
    | 15    |    5 |Casey   |
    |       |      |        |
    | 4     |  145 |You     |
    +-------+------+--------+

So, is there some way to do it in one query?

Thanks!

Konstantin
  • 1,150
  • 13
  • 31
  • please add a `DESCRIBE` or `SHOW CREATE TABLE` of your table structure! – Kaii Feb 27 '14 at 17:24
  • So each player has a unique value for `level`? Or can multiple players have the same level? – Patrick Q Feb 27 '14 at 17:27
  • You may want to look into the Rank() function. This is a similar question. The answer may be helpful to you. http://stackoverflow.com/questions/3333665/mysql-rank-function – Kevin Feb 27 '14 at 17:31
  • Nope, level is not a unique key. A lot of users can have the same level. And it doesn't really matter what table structure i have, there's only two colums which are expected to be used here, they are: level (int(5)) and user_id (varchar(40)). The table itself is called values – Konstantin Feb 28 '14 at 04:02

3 Answers3

2

A union can do it, see pseudo code to get the idea

(
SELECT `values`.`level` FROM `values` ORDER BY `values`.`level` DESC LIMIT 10
UNION
SELECT `values`.`level` FROM `values` where playerId = loggedPlayerId
) ORDER BY `values`.`level`
jean
  • 4,159
  • 4
  • 31
  • 52
  • You may want to modify that second `select` to show only that most recent score. – Zane Feb 27 '14 at 18:38
  • This code will only return what level the user has. It's not what I'm expecting. I can get user's level easily, but I don't need that. Imagine this: there's a large sorted list of user levels. For example 1) Alex - level 23, 2) James - level 19, 3) John - level 17.... 145) You - level 2 – Konstantin Feb 28 '14 at 03:57
  • So in this example, I'm looking for a way to get that 145 (or whatever number it may be) just to show a user what position he has on a top users list – Konstantin Feb 28 '14 at 04:05
  • I've edited my question a little bit, just to clarify the idea of what I want to get as a result – Konstantin Feb 28 '14 at 04:48
0

Is this what you are expecting.

(
SET @rank=0;
SELECT 
`values`.`level`,
@rank:=@rank+1 AS pos, 
`values`.userID
FROM 
`values` 
ORDER BY `values`.`level` DESC 
LIMIT 10;
)
UNION
(
SELECT 
v1.`level`,
COUNT(*) as pos, 
v1.userID
FROM 
`values` AS v1
LEFT JOIN
`values` AS v2
ON( v1.level < v2.level )
WHERE v1.userID = 24342
GROUP BY 1
)

I have assumed that there would be a column named userID present in the table named values.

Manu
  • 901
  • 1
  • 8
  • 28
0

Consider the following (although this will scale poorly on a large data set)...

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT x.*
     , COUNT(*) rank 
  FROM ints x 
  JOIN ints y 
    ON y.i <= x.i 
 GROUP 
    BY x.i 
HAVING rank <=3 
    OR i=7;
+---+------+
| i | rank |
+---+------+
| 0 |    1 |
| 1 |    2 |
| 2 |    3 |
| 7 |    8 |
+---+------+

I suspect that something like this will scale better...

SELECT * 
  FROM 
     ( SELECT i.*
            , @i:=@i+1 rank 
         FROM ints i
            , (SELECT @i:=0) n 
        ORDER 
           BY i
     ) x 
 WHERE rank <= 3 
    OR i = 7;

+---+------+
| i | rank |
+---+------+
| 0 |    1 |
| 1 |    2 |
| 2 |    3 |
| 7 |    8 |
+---+------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • The problem with this code is that it will return all users level. But I need only 10. Well, actually 11, but he evelenth position must show "my" rank.`code +-------+------+--------+ | level | rank |user_id | +-------+------+--------+ | 23 | 1 |Alex | | 19 | 2 |John | | 10 | 3 |James | | | | | | 4 | 145 |You | +-------+------+--------+` – Konstantin Feb 28 '14 at 04:19
  • This query returns (in this example) 3 (out of a possible 10) rows, plus the user's rank if it falls outside that 3-row range. – Strawberry Feb 28 '14 at 08:08