Sorting by some column and also by rand() in MySQL

Question

Is it possible to sort a result set by some column and also by RAND()?

For example:

  SELECT `a`, `b`, `c` 
    FROM `table` 
ORDER BY `a` DESC, RAND() 
   LIMIT 0, 10

Thank you.

Answer 1

What you are doing is valid - it will order the results in descending order by a but randomize the order of ties.

However to do what you want you need to first use a subquery to get the latest 100 records and then afterwards sort the results of that subquery randomly using an outer query:

SELECT * FROM
(
    SELECT * FROM table1
    ORDER BY date DESC
    LIMIT 100
) T1
ORDER BY RAND()