"Fine tuning" in SELECT for a ranking system

Question

"Fine tuning" in SELECT for a ranking system

Navigation

#1 by (3 votes)

2

I'm having difficulty using MySQL.

I've been asking here already, and I've done a lot of testing and progress in crafting a select for a ranking .

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

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

My current query :

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 tips of the table.

I'm asking for help

mysql select

asked by anonymous 12.02.2016 / 02:16

1 answer

Timer Get the executable directory and save image to a folder inside this directory

score 3 · Answer 1

ORDER was missing in first SELECT s.

(
  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

Only you have a problem!
the query post in the question has a bug, draws are always out.

Solution for the tie bug:

By adding the id_jogador field 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 display the tied below.

Toast, we can still make a SELECT less:

(
  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 in SQL Fiddle .

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

If you prefer that ties appear earlier, just reverse logic and use <= in SELECT down, and > on top. Here the% w / w% and% w /% w / w% are set.