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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I need to consult a table by extracting from it the infos: <nome> and <pontuacao> , my tcc is a game where one of your options is to see a Ranking of players registered. as there is a possibility that the soft grow in number of users this query can become very heavy if it is to return all the tuples of the table.

What I want is to be able to recover the position of the current player and 5 positions above and 5 below to ease the load.

What I was able to do was:

select nome,pontuacao from jogador order by pontuacao DESC;

It does the whole table search and this can hurt the system later.

Would anyone know how to limit this query?

sql query

asked by anonymous 10.09.2015 / 19:31

2 answers

.Parameters.Append .CreateParameter is not passing the second parameter How do I keep the colors of items that have changed colors in the listView on Android?

score 0 · Answer 1

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

See: link

score 0 · Answer 2

If the DBMS in question is Oracle , Sql Server or PostgreSQL , you can use 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 that gets the name of the current player.