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
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.
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.
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.
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.
Beware of ORDER BY RAND() because of performance and results. Check this article out: http://jan.kneschke.de/projects/mysql/order-by-rand/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With