MySQL ORDER BY rand(), name ASC

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

Beware of ORDER BY RAND() because of performance and results. Check this article out: http://jan.kneschke.de/projects/mysql/order-by-rand/

Answer 5

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;