Ranking database [#1 of 10]

Question

Ranking database [#1 of 10]

I need to rank the DB Records during paging. At first I do not intend to create a field for the ranking. Assuming having 10 records

_{1) Ordenando a paginação por AZ, quero listar com o número correspondente ao rank - #N de 10}
_{2) Quando exibir o conteúdo, preciso o número correspondente ao rank - #N de 10}

_{• paging}
_{#01 of 10, Lorem ipsum}
_{#02 of 10, Lorem ipsum}
_...
_{#10 of 10, Lorem ipsum}

SELECT *,
FIND_IN_SET( score,
    ( SELECT GROUP_CONCAT( score ORDER BY score DESC ) FROM table )
) AS rank
FROM table limit X,X

_{• Content}
_{Lorem ipsum, Ranking #03 of 10}

SELECT *,
FIND_IN_SET( score,
    ( SELECT GROUP_CONCAT( score ORDER BY score DESC ) FROM table )
) AS rank
FROM table WHERE ...

I took this SQL from an example and it's working, but I don't master MYSQL and have no knowledge for this case...
I would like to know if there is another way to mount this QUERY... I accept suggestions.

5

mysql banco-de-dados paginação

Author: Maniero, 2014-08-16

Source

2 answers

score 4 · Answer 1

I don't understand if that's what you want, but if it's just the numbering of the output lines, you can do something like this:

SELECT
    @linha := @linha + 1 AS contador,
    tabela_desejada.*
FROM
    (SELECT @linha := 35) AS nada,
    --                 ^ Usar o mesmo valor inicial do limit
    tabela_desejada
LIMIT 35,10;
--     ^ Usar o mesmo valor inicial da subquery

note that the subquery with example Value 35 has to use the same initial value as the limit. Probably, since the query will be generated dynamically, it is enough to use the same parameter in both places.

also remember that MySQL's limit starts from scratch.
To start from 1 in the output, just do ( @linha := @linha + 1 ) + 1 ...

version with totalizer:

SELECT
    CONCAT( "#", @linha := @linha + 1, " de ", total.cnt ) AS ranking,
    tabela_desejada.*
FROM
    (SELECT @linha := 35) AS nada,
    (SELECT COUNT(*) AS cnt FROM tabela_desejada) AS total,
    tabela_desejada
ORDER BY nome
LIMIT 35,10;

In this case, if you are going to use WHERE, Remember to replicate the condition in the query main and in the subquery :

...
    (SELECT COUNT(*) AS cnt FROM tabela_desejada WHERE id > 100) AS total,
    --                                                     ^ atenção
    tabela_desejada
WHERE id > 100
--       ^ atenção
ORDER BY nome
LIMIT 35,10;

score 1 · Answer 2

Based on your example, just modify the subselect fields to match your criteria: (get rank ordered by AaZ name)

SELECT *,
FIND_IN_SET( id,
    ( SELECT GROUP_CONCAT( id ORDER BY nome ASC ) FROM `table` )
) AS rank
FROM `table` order by nome;

And to take a record:

SELECT *,
FIND_IN_SET( id,
    ( SELECT GROUP_CONCAT( id ORDER BY nome ASC ) FROM `table` )
) AS rank,
( SELECT COUNT( id ) FROM `table` ) AS total
FROM `table` where id=5;

That is, FIND_IN_SET looks for id 5 in the list of ids (1,3,5,8,4,10,9,6,7,2) that was ordered by name, returning position 3...

See how sqlfiddle works: http://sqlfiddle.com/#! 2 / 9a84a9/4