Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL ORDER BY rand(), name ASC

I would like to take a database of say, 1000 users and select 20 random ones (ORDER BY rand(),LIMIT 20) then order the resulting set by the names. I came up with the following query which is not working like I hoped.

SELECT * FROM users WHERE 1 ORDER BY rand(), name ASC LIMIT 20

like image 514
Josh K Avatar asked May 21 '10 13:05

Josh K


People also ask

What is ASC in MySQL?

The ASC stands for ascending and the DESC stands for descending. You use ASC to sort the result set in ascending order and DESC to sort the result set in descending order respectively.

How do I arrange ascending order in MySQL?

The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

How do I change the order of rows in MySQL?

An "ALTER TABLE ORDER BY" statement exist in the syntaxes accepted by MySQL. According to the documentation, this syntax: - only accept *one* column, as in "ALTER TABLE t ORDER BY col;" - is used to reorder physically the rows in a table, for optimizations.


2 Answers

Use a subquery:

SELECT * FROM  (     SELECT * FROM users ORDER BY rand() LIMIT 20 ) T1 ORDER BY name  

The inner query selects 20 users at random and the outer query orders the selected users by name.

like image 149
Mark Byers Avatar answered Sep 23 '22 17:09

Mark Byers


Beware of ORDER BY RAND() because of performance and results. Check this article out: http://jan.kneschke.de/projects/mysql/order-by-rand/

like image 31
ircmaxell Avatar answered Sep 25 '22 17:09

ircmaxell