Randomized and ascending order query in MYSQL

Asked

Viewed 657 times

4

I have a table where I have the student’s name and his score, I need to pick up 5 random students and display them in ascending order of the score.

I tried to do it this way, but he’s just randomly bringing in the students and he’s not ordering the scores:

SELECT * FROM `aluno` ORDER BY RAND() , `AlunoPontos` ASC LIMIT 0 , 5

Does anyone know how I can do this kind of consultation ?

Thank you.

php mysql random order-by

  • Here is the answer to the question: http://answall.com/questions/108571/select-rand%C3%B4mico-com-sqlserver-mssql/108575#108575

  • 3

    It’s either random or ascendant :P

  • Take a look at this -> http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast

  • You managed to solve the problem?

  • I haven’t made it yet, @Marcoauroéliodeleu

1 answer

5


One way to achieve the result you expect is to use sub-query.

SELECT * FROM (
      SELECT * FROM `aluno` ORDER BY RAND() LIMIT 0, 5
) as alunosAleatorios
ORDER BY `AlunoPontos` ASC

Thus, the internal query scrambles the table and returns only the first 5 results. Then, the external query causes the 5 obtained results to be sorted by the column AlunoPontos in order ASC.

Internal Query

SELECT * FROM `aluno` ORDER BY RAND() LIMIT 0, 5

External Query

SELECT * FROM () as alunosAleatorios ORDER BY `AlunoPontos` ASC

Browser other questions tagged php mysql random order-by

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