How to Select a Random Subset and Then Order by Another Column in MySQL?

Random Selection and Ordering in MySQL

When querying a MySQL database, it is common to want to select a subset of records randomly. The ORDER BY RAND() clause can be used to achieve this. However, it is important to consider the order of operations when combining this clause with other ordering clauses.

One common pitfall occurs when attempting to select a random subset of records and then sort the resulting set by another column. The following query is an example of this:

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

The intention of this query is to select 20 random users from the users table and then order them in ascending order by their name column. However, this query will not produce the desired result.

The reason for this is that the ORDER BY RAND() clause introduces a non-deterministic ordering. This means that the order of the results will change every time the query is executed. As a result, the subsequent name ASC clause cannot be used to reliably order the results.

To correctly order the results, we need to use a subquery. The following query will select 20 random users and then order them by their name column:

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

The inner query selects 20 users at random and stores the result in the temporary table T1. The outer query then selects all rows from T1 and orders them by name.

The above is the detailed content of How to Select a Random Subset and Then Order by Another Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!