I need to rank DB records during pagination. At first I do not intend to create a field for ranking. Assuming you have 10 records
1) Ordenando a paginação por AZ, quero listar com o número correspondente ao rank - #N de 10
Lorem ipsum
I do not understand if this is what you want, but if it is 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 the value of example 35 must use the same initial value of the limit. Probably, because the query will be generated dynamically, it is enough to use the same parameter in both places.
- Also remember that
limitof MySQL starts from scratch.
To start from 1 at the output, just do( @linha := @linha + 1 ) + 1 ...
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 query main and in 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;
Based on your example, simply modify the subselect fields to fit 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 get 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 sorted by name, returning to position 3 ...
See how sqlfiddle works: link