MySQL - How do I order the results randomly inside a column?

Question

I need to retrieve rows from a table (i.e: 'orders') ordered by a column (lets say 'user') randomly. That is, I need all orders from the same user to remain together (one after the other) and users to be ordered randomly.

Answer 1

How random does it have to be? I can think of a few possible answers.

If the "random" sequence should be repeatable, you can sort by a hash of the user ID, using MD5 or a custom one you create yourself e.g. ORDER BY MD5(), secondary_sort_column.

Answer 2

I'm going to assume you have a second table called "users" that has all the users in it. If not, you could still do this by adding another SELECT DISTINCT subquery on orders, but that would be much messier:

SELECT orders.* 
FROM orders 
INNER JOIN (SELECT userid, RAND() as random FROM users) tmp
ON orders.userid = tmp.userid
ORDER BY tmp.random, tmp.userid

You'll want to order by the random number AND the user id so if two user ids get the same random number their orders won't be all jumbled together.

Answer 3

order by reverse(user) ?