Selecting Random Items from a Set of Rows : RAND « Function « SQL / MySQL






Selecting Random Items from a Set of Rows

     
mysql>
mysql>
mysql> CREATE TABLE die
    -> (
    ->  n       INT
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO die (n) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO die (n) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO die (n) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO die (n) VALUES(4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO die (n) VALUES(5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO die (n) VALUES(6);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM die;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

mysql>
mysql> SELECT n FROM die ORDER BY RAND( ) LIMIT 1;
+------+
| n    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

mysql> SELECT n FROM die ORDER BY RAND( ) LIMIT 1;
+------+
| n    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT n FROM die ORDER BY RAND( ) LIMIT 1;
+------+
| n    |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

mysql> SELECT n FROM die ORDER BY RAND( ) LIMIT 1;
+------+
| n    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> drop table die;
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.Add a column of random numbers to the column output list, alias it, and refer to the alias for sorting
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