Add a column of random numbers to the column output list, alias it, and refer to the alias for sorting

     
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)

Related examples in the same category

1.	Each time you seed the generator with a given value, RAND( ) will produce a repeatable series of numbers:
2.	Selection with a random Column
3.	To select a single winner at random from the combined results of the three tables
4.	Generating Random Numbers
5.	Seed RAND( ) randomly
6.	Randomizing a Set of Rows
7.	Selecting Random Items from a Set of Rows
8.	To draw five winning entries at random from a table named drawing that contains contest entries, use RAND( ) i
9.	get some random numbers
10.	Get some random numbers in the range 1-100
11.	Get the name and the number of each player whose name consists of the pattern m.n. The point can be any random

Add a column of random numbers to the column output list, alias it, and refer to the alias for sorting : RAND « Function « SQL / MySQL

Related examples in the same category