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

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/

Instead of using a subquery, you could use two separate queries, one to get the number of rows and the other to select the random rows.

SELECT COUNT(id) FROM users; #id is the primary key

Then, get a random twenty rows.

$start_row = mt_rand(0, $total_rows - 20);

The final query:

SELECT * FROM users ORDER BY name ASC LIMIT $start_row, 20;

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

Use a subquery:

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

or a join to itself:

SELECT * FROM users u1
INNER JOIN (
    SELECT id FROM users ORDER BY RAND() LIMIT 20
) u2 USING(id)
ORDER BY u1.name

MySQL ORDER BY rand(), name ASC

Related

Recent Posts