ORDER BY column ASC RAND() - Help

[curb]

I'm trying to have the first column (home) order by ascending while randomizing. The problem is that my second order gets affected by this as well. Is there a way I can randomize column1 while having column2 to stay put without using UNION?


ORDER BY `column1` ASC,RAND(), `column2` DESC

[Fumigator]

Straight out of the MySQL manual:

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

[guelphdad]

how do you mean you want a column ordered in ascending but also randomized order? if you mean you want say 10 rows but randomly selected from your table and then when those rows are returned they are in ascending order, then the rand belongs in your select statement and not the order by clause.

perhaps you could illustrate by example what you are looking to achieve.

[curb]

Here's an example:

DATA

CLASSROOM | TIME SUBMITTED
math | April 7, 2007
math | April 6, 2007
math | April 5, 2007
math | April 4, 2007
english | April 3, 2007
english | April 2, 2007
english | April 1, 2007


I'm trying to randomize the column that has math but keep the 'english' column in order (desc order) so it may look like this when randomized:

CLASSROOM | TIME SUBMITTED
math | April 6, 2007
math | April 4, 2007
math | April 5, 2007
math | April 7, 2007
english | April 3, 2007
english | April 2, 2007
english | April 1, 2007

[guelphdad]

I would just use a UNION.