"Fine tuning" in SELECT for a ranking system

Question

I’m having difficulties using Mysql.

I’ve been asking here already, and I’ve done several tests and progress in the elaboration of a select for a ranking.

I have a "player" table that guards among other elements, the nome_jogador and pontuacao. These two Infos are necessary for me to create a query that returns a ranking, being the current player of the app (always in the center unless it is first or last place), above the players (some) with the score immediately higher than the player’s score chosen, and below the players with the score immediately lower than his.

In the query I managed to do this, but if I have a table with 30 players, my query reveals the current score, and the highest and lowest player, ignoring the closest intermediaries, which are the ones I need.

My query current:

select nome_jogador,pontuacao 
from jogador 
where pontuacao > (select pontuacao from jogador where id_jogador='4') limit 2 

union

select nome_jogador,pontuacao 
from jogador 
where pontuacao < (select pontuacao from jogador where id_jogador='4') limit 2

union

select nome_jogador,pontuacao 
from jogador 
where id_jogador='4' order by pontuacao desc;

I want to adjust this query to show the others closest to the current player above and below. not the table tips.

I earnestly ask for help

Answer 1

Missed the ORDER in the first SELECTs.

(
  SELECT nome_jogador, pontuacao
    FROM jogador 
    WHERE pontuacao > (SELECT pontuacao FROM jogador WHERE id_jogador=4 )
    ORDER BY pontuacao
    LIMIT 2
)
UNION
(
  SELECT nome_jogador, pontuacao
    FROM jogador 
    WHERE pontuacao < (SELECT pontuacao FROM jogador WHERE id_jogador=4 )
    ORDER BY pontuacao DESC
    LIMIT 2
)
UNION
(
  SELECT nome_jogador, pontuacao
    FROM jogador 
    WHERE id_jogador=4
)
ORDER BY pontuacao DESC

There’s just one little problem!
to query Asked question has a bug, draws always stay out.

Solution for tie bug:

Adding the field id_jogador in the query we can ensure that the user is always the middle one (if there is enough before or after it) and at the same time show the tied ones just below.

For free, we can still make one SELECT unless:

(
  SELECT id_jogador, nome_jogador, pontuacao
    FROM jogador 
    WHERE pontuacao >= (SELECT pontuacao FROM jogador WHERE id_jogador=4 )
    ORDER BY pontuacao
    LIMIT 3
)
UNION
(
  SELECT id_jogador, nome_jogador, pontuacao
    FROM jogador 
    WHERE pontuacao < (SELECT pontuacao FROM jogador WHERE id_jogador=4 )
    ORDER BY pontuacao DESC
    LIMIT 2
)
ORDER BY pontuacao, id_jogador!=4

See working on SQL Fiddle.

If you prefer not to return the id_jogador also works, but then the order of draws can take the desired player from the middle line.

If you prefer ties to appear before, just reverse the logic and use <= in the SELECT from below, and > on the top. There the LIMIT and the != of ORDER.