MySQL ORDER BY rand(), name ASC

Question

I want 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 tried the following query which is not working as 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 will select 20 users at random and the outer query will order the selected users by name.

Willing to Learn SQL? Join this SQL training course and certification program by Intellipaat.

You can also go through the below MySQL tutorial video that will help you out in a better way: