How to consult a table in a limited (partial) way?

Asked

Viewed 165 times

0

I need to consult a table extracting from it the Infos: <nome> and <pontuacao>, my tcc consists of a game in which one of its options is to see a ranking of registered players. as there is the possibility of soft growing in number of users this query may become very heavy if it is to return all tuples of the table.

What I wish is to be able to regain the current player’s position and 5 positions above and 5 below to relieve the load.

What I managed to do was: 

select nome,pontuacao from jogador order by pontuacao DESC;

which makes the whole table search and this can harm the system further.

Would anyone know how to limit this query?

sql query

  • Luis, which DBMS do you use? Sql Server, Mysql, Postgresql, etc?

  • If it is possible to change the business rule (show the player’s position plus the 5 top and 5 bottom positions), I would advise to bring only one Top10 through the use of syntax top() of SQL, one more union to return the current position of the player.

  • I will apply in Mysql.

  • Luiz, I saw your comment just now, unfortunately Mysql is very limited on resources, you could even try to take advantage of my answer and use Subqueries instead of CTE and emulate ROW_NUMBER, but the performance would not be very encouraging, another possibility is to consider using Postgresql instead of Mysql.

  • How would this query be ready in Postgresql ??? If it is good so it may be worth changing ... would change something???

  • Perhaps I would have to make a few modifications, but the logic would be the same. Anything posts a new question.

Show 1 more comment

2 answers

0

If the DBMS concerned is Oracle, the Sql Server or the PostgreSQL, you can use a CTE with ROW_NUMBER:

WITH cte_rank AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY pontuacao DESC) AS pos_rank,
        nome,
        pontuacao 
    from jogador
), cte_meu_rank AS (
    SELECT pos_rank
    FROM cte_rank
    WHERE nome = :nome
)

select 
    cte_rank.pos_rank,
    cte_rank.nome,
    cte_rank.pontuacao
FROM cte_meu_rank, cte_rank
WHERE cte_rank.pos_rank between cte_meu_rank.pos_rank - 5 AND cte_meu_rank.pos_rank + 5

where :nome is a variable named after the current player.

0

You can use the top of SQL. In this case you can filter by punctuation. For example: "select TOP(5) name, score from player Where score order by score > DESC;". If the query is the first the "last score consulted" will be 0, otherwise as the name says will be the "last score consulted".

Behold: http://www.w3schools.com/sql/sql_top.asp

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.