random order within mysql order by case

Question

My query selects results correctly using ORDER BY CASE. I would like to randomize the results within EACH case. i.e. ORDER BY CASE WHEN apples (randomize) THEN 1 WHEN pears (randomize) THEN 2, etc. So my results are still ordered by each case but within the results PER case they are random, each time the query is run.

Answer 1

You can try ORDER BY CASE, RAND(). RAND() generates a random number, of course.

However, I heard somewhere that shuffling elements in SQL is not quite the most efficient way and it is better to randomize elements in PHP. But randomizing them per case is not a trivial task. (PHP rand() function)

Answer 2

Often times for simplicity I will throw a NewID() function into a SQL Order By to randomize the order of the results. In your case, you could use the uniqid(), or if you really are passing the query straight to MSSQL, you could use NEWID() as well...