Mega Code Archive
Add a column of random numbers to the column output list, alias it, and refer to the alias for sorting
<![CDATA[
mysql>
mysql> CREATE TABLE testscore
-> (
-> subject INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> age INT UNSIGNED NOT NULL,
-> sex ENUM('M','F') NOT NULL,
-> score INT,
-> PRIMARY KEY (subject)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO testscore (age,sex,score)
-> VALUES
-> (5,'M',5),
-> (5,'M',4),
-> (5,'F',6),
-> (5,'F',7),
-> (6,'M',8),
-> (6,'M',9),
-> (6,'F',4),
-> (6,'F',6),
-> (7,'M',8),
-> (7,'M',6),
-> (7,'F',9),
-> (7,'F',7),
-> (8,'M',9),
-> (8,'M',6),
-> (8,'F',7),
-> (8,'F',10),
-> (9,'M',9),
-> (9,'M',7),
-> (9,'F',10),
-> (9,'F',9)
-> ;
Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT score, score*0+RAND( ) AS rand_num FROM testscore ORDER BY rand_num;
+-------+--------------------+
| score | rand_num |
+-------+--------------------+
| 9 | 0.022593753433408 |
| 6 | 0.0526860934241545 |
| 9 | 0.0827481160711014 |
| 7 | 0.197235208188403 |
| 7 | 0.264066166490378 |
| 7 | 0.365396586587091 |
| 9 | 0.564859714885112 |
| 9 | 0.615011072359058 |
| 10 | 0.651087478409603 |
| 9 | 0.663731798216451 |
| 4 | 0.666073742942953 |
| 7 | 0.681978580245728 |
| 8 | 0.762958444434179 |
| 6 | 0.778362864422019 |
| 8 | 0.791150104059978 |
| 6 | 0.797692270761069 |
| 4 | 0.824093464598147 |
| 5 | 0.825970424177098 |
| 10 | 0.835787569019746 |
| 6 | 0.852457472917119 |
+-------+--------------------+
20 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table testscore;
Query OK, 0 rows affected (0.00 sec)
]]>