[MYSQL] Seite mit Highscore-Listen, Rangabfrage

[thomas_w]

Noch zwei andere Ideen dazu:

Version 1 mit BETWEEN

SELECT page.rank, page.points 
  FROM (SELECT CASE
                WHEN @prev_points <> p.points THEN @rank := @rank + 1
                ELSE @rank
               END                             AS rank,
               @prev_points := p.points          AS points
         FROM  (SELECT points + points + points AS points from players ) AS p,
             (SELECT @rank := 0, @prev_points := 0) rank
        ORDER BY p.points DESC
       ) AS page
 WHERE page.rank BETWEEN 2 AND 4
 ORDER BY page.rank;

+------+--------+
| rank | points |
+------+--------+
|    2 |     60 |
|    2 |     60 |
|    3 |     30 |
|    3 |     30 |
+------+--------+
4 rows in set (0.00 sec)

und die 2. Variante mit LIMIT

SELECT page.rank, page.points 
  FROM (SELECT CASE
                WHEN @prev_points <> p.points THEN @rank := @rank + 1
                ELSE @rank
               END                             AS rank,
               @prev_points := p.points          AS points
         FROM  (SELECT points + points + points AS points from players ) AS p,
             (SELECT @rank := 0, @prev_points := 0) rank
        ORDER BY p.points DESC
       ) AS page
 ORDER BY page.rank
 LIMIT 1, 2;

+------+--------+
| rank | points |
+------+--------+
|    2 |     60 |
|    2 |     60 |
+------+--------+
2 rows in set (0.00 sec)

mysql>

Ob eine Abfrage performant ist, läßt sich messen bzw. mit EXPLAIN kontrollieren..

EXPLAIN SELECT ..

Grüße
Thomas