How to Randomly Select and Sort Data in MySQL?

Randomizing and Sorting MySQL Data

In MySQL, you can combine the ORDER BY clause with the rand() function to retrieve random records from a table. However, if you attempt to further sort the randomized results using another column, you may encounter unexpected behavior.

To address this, the query should be structured as a subquery. The inner query retrieves 20 random users using ORDER BY rand() LIMIT 20.

<br>SELECT * FROM users ORDER BY rand() LIMIT 20<br>

The resulting temporary table, which contains the random users, is then referenced by the outer query. The outer query re-sorts the temporary table by the name column in ascending order:

<br>SELECT * FROM <br>(</p>
<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">SELECT * FROM users ORDER BY rand() LIMIT 20

Copy after login

) T1
ORDER BY name ASC

This approach ensures that the final result set consists of 20 randomly selected users, ordered by their names in ascending order.

The above is the detailed content of How to Randomly Select and Sort Data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!