MySQL ORDER BY rand(), name ASC

MySQL ORDER BY rand(), name ASC

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Why ORDER BY rand(), name ASC Doesn't Give You Randomized Results

The query pattern you encountered is a common stumbling block for developers transitioning from relational database design to data manipulation patterns that prioritize randomness over strict ordering. The specific goal here—selecting 20 random users and then sorting them alphabetically by name—is conceptually straightforward, but the execution plan generated by MySQL reveals why your initial attempt fails to produce the expected outcome.

When you execute SELECT * FROM users WHERE 1 ORDER BY rand(), name ASC LIMIT 20, you are instructing the database engine to perform two distinct operations in a specific sequence that often defies intuition. The ORDER BY clause dictates how rows are sorted before any limiting occurs. However, when multiple sorting columns are involved, MySQL applies them hierarchically: it sorts by the first column (rand()) and only uses subsequent columns (name ASC) to resolve ties where the primary sort key is identical.

The fundamental issue lies in the behavior of RAND() within a standard SQL query context. Unlike functions that operate on individual rows in isolation during an aggregate operation, RAND() generates a new random value for every row processed. In your specific query structure, the database engine calculates a random number for every single user in the table. Because these numbers are continuous and effectively unique across the entire dataset (unless you have millions of rows and limited precision issues), it is statistically virtually impossible for any two users to share the exact same random value.

Consequently, when the database sorts the results, the name ASC clause becomes irrelevant. Since every row has a unique primary sort key derived from rand(), there are no ties for the secondary key to resolve. The resulting set is purely randomized, and the alphabetical order of names is completely lost before the LIMIT 20 clause even comes into play to trim the result set down to 20 rows.

To achieve the desired effect of a random subset followed by an alphabetically sorted list, you must ensure that the randomization happens after the selection or in a way that allows the secondary sort to take precedence on the final output. One robust approach involves using a subquery or a derived table. This method separates the random selection logic from the final ordering logic.

You can execute the following pattern:

SELECT * FROM (
    SELECT * FROM users ORDER BY RAND() LIMIT 20
) AS temp_table
ORDER BY name ASC;

In this structure, the inner query first performs the randomization and truncation, isolating exactly 20 rows based on their random values. Once this subset is materialized in the temp_table, the outer query sorts those specific 20 rows by the name column. Because the ORDER BY rand() has already filtered the data down to a small, fixed set, the database no longer needs to worry about tie-breaking across the entire table, and the alphabetical sort operates as intended on the narrowed-down results.

Alternatively, if performance is a concern with larger datasets, consider generating random values in an application layer using your framework's query builder or raw SQL injection mechanisms. For instance, using Laravel's query builder, you could construct a similar logic where you scope the query to limit first, then order:

$users = User::inRandomOrder()->limit(20)->get();
// Then sort in PHP or via another query if strict DB-side ordering is needed

However, relying on ORDER BY rand() followed by a secondary sort column usually indicates a misunderstanding of how hierarchical sorting interacts with row-level functions. The key takeaway is that secondary sort keys only function when the primary key produces duplicates. Since RAND() rarely produces duplicates in a single pass across a table, the secondary sort is effectively bypassed unless you isolate the random selection first.

Our network