Rank database [#1 of 10]

Asked

Viewed 544 times

5

I need to rank the DB records during paging. I don’t intend to create a field for ranking at first. Assuming I have 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

• PAGINATION
#01 of 10, Lorem ipsum
#02 of 10, Lorem ipsum
...
#10 out 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 understand MYSQL and I don’t know for this case...
I wonder if there is another way to assemble this QUERY... I accept suggestions.

mysql database paging
  • 1

    To facilitate understanding you would need to post the database table structure used!

  • 1

    @Willams de Jesus, do not need the structure of DB because I want to order in ascending order and there is a key worth as score. The example I posted on stackexchange - works but is based on points(TBL-SCORE)

  • 1

    What do you call rank? The line number in the result?

  • 1

    I don’t quite understand. So you don’t want a field like the score of your examples? And such numbering would be based on what? In the alphabetical order of the results of each page?

  • @bfavaretto yes, assuming that the ID 05 in the pagination is listed as #2 in the AZ rank, I would also like that when accessing the content it also does the calculation to display Posição 2 de 10.

2 answers

4

I don’t understand if this is 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 the example value 35 you have to use the same limit starting value. Probably, as the query will be generated dynamically, just use the same parameter in both places.
  • Also remember that the limit Mysql starts from scratch.
    To start from 1 on 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 will use WHERE, remember to replicate the condition in query leading 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;
  • 1

    @Papacharlie if you really want to complicate :), and make sure the ranking doesn’t change with WHERE, the way is to pass the counter into the subquery. Thus, the count is done on the whole basis, before the result is filtered (with a penalty in processing, obviously).

  • I used as a criterion the AZ order in order to simplify purposefully, because I need to understand how QUERY works, and then apply to needs using some averages. I will put your example into practice and then give a feedback. + 1 for the explanations given.

1

Based on your example, simply modify the subselect fields to fit your criteria: (get the rank ordered by the name of Aaz)

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 ordered by name, returning to position 3...

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

Browser other questions tagged mysql database paging

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