Simple Leaderboard rankings from mysql database

Question

I'm making a simple game, and I want to make a mini-leaderboard the user can view. The leaderboard would consist of the user, the 5 players above him in point total, and the 5 players below him in point total. How would I make a MySQL query to return these users?

For example: In the below table, when given the user's id as 8, I would want to return the players (and their points) of Lea, Joshua, Pete (since there aren't 5 above him in this case), John, Bert, Maxi, Max, Anna.

id| name |   points
1   Max       20
2   anna      10
3   bert      30
4   maxi      30
5   lea       100
6   lei       10
7   joshua    90
8   Pete      50
9   John      40

Answer 1

If you are running MySQL 8.0, you can use row_number() and a common table expression:

with cte as (
    select t.*, row_number() over(order by points) rn
    from mytable t
)
select c.id, c.name, c.points
from cte c
inner join (select rn from cte where id = 8) r
    on c.rn between r.rn - 5 and r.rn + 5