The RANDOM function generates a random value between 0.0 (inclusive) and 1.0 (exclusive).
Syntax
RANDOM()
Return type
DOUBLE PRECISION
Usage notes
Call RANDOM after setting a seed value with the SET command to cause RANDOM to generate numbers in a predictable sequence.
Examples
To compute a random value between 0 and 99, use the following example. If the random number is 0 to 1, this query produces a random number from 0 to 100.
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 59 |
+------+This example uses the SET command to set a SEED value so that RANDOM generates a predictable sequence of numbers.
To return three RANDOM integers without setting the SEED value, use the following example.
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 6 |
+------+
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 68 |
+------+
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 56 |
+------+To set the SEED value to .25, and return three more
RANDOM numbers, use the following example.
SET SEED TO .25;
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 21 |
+------+
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 79 |
+------+
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 12 |
+------+To reset the SEED value to .25, and verify that
RANDOM returns the same results as the previous three calls, use the following example.
SET SEED TO .25;
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 21 |
+------+
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 79 |
+------+
SELECT CAST(RANDOM() * 100 AS INT);
+------+
| int4 |
+------+
| 12 |
+------+The following examples use the TICKIT sample database. For more information, see Sample database.
To retrieve a uniform random sample of 10 items from the SALES table, use the following example.
SELECT *
FROM sales
ORDER BY RANDOM()
LIMIT 10;
+---------+--------+----------+---------+---------+--------+---------+-----------+------------+---------------------+
| salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission | saletime |
+---------+--------+----------+---------+---------+--------+---------+-----------+------------+---------------------+
| 45422 | 51114 | 5983 | 24482 | 4369 | 2118 | 1 | 195 | 29.25 | 2008-10-19 05:20:07 |
| 42481 | 47638 | 4573 | 6198 | 6479 | 1987 | 4 | 1140 | 171 | 2008-06-10 09:39:19 |
| 31494 | 34759 | 18895 | 4719 | 7753 | 2090 | 4 | 1024 | 153.6 | 2008-09-21 03:44:26 |
| 119388 | 136685 | 21815 | 41905 | 2071 | 1884 | 1 | 359 | 53.85 | 2008-02-27 10:43:10 |
| 166990 | 225037 | 18529 | 7628 | 746 | 2113 | 1 | 2009 | 301.35 | 2008-10-14 10:07:44 |
| 11146 | 12096 | 42685 | 6619 | 1876 | 2123 | 1 | 29 | 4.35 | 2008-10-24 06:23:54 |
| 148537 | 172056 | 15102 | 11787 | 6122 | 1923 | 2 | 480 | 72 | 2008-04-07 03:58:23 |
| 68945 | 78387 | 7359 | 18323 | 6636 | 1910 | 1 | 457 | 68.55 | 2008-03-25 08:31:03 |
| 52796 | 59576 | 9909 | 15102 | 7958 | 1951 | 1 | 479 | 71.85 | 2008-05-05 02:25:08 |
| 90684 | 103522 | 38052 | 21549 | 7384 | 2117 | 1 | 313 | 46.95 | 2008-10-18 05:43:11 |
+---------+--------+----------+---------+---------+--------+---------+-----------+------------+---------------------+To retrieve a random sample of 10 items, but choose the items in proportion to their prices, use the following example. For example, an item that is twice the price of another would be twice as likely to appear in the query results.
SELECT *
FROM sales
ORDER BY -LOG(RANDOM()) / pricepaid
LIMIT 10;
+---------+--------+----------+---------+---------+--------+---------+-----------+------------+---------------------+
| salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission | saletime |
+---------+--------+----------+---------+---------+--------+---------+-----------+------------+---------------------+
| 158340 | 208208 | 17082 | 42018 | 1211 | 2160 | 4 | 6852 | 1027.8 | 2008-11-30 12:21:43 |
| 53250 | 60069 | 12644 | 7066 | 7942 | 1838 | 4 | 1528 | 229.2 | 2008-01-12 11:24:56 |
| 22929 | 24938 | 47314 | 6503 | 179 | 2000 | 3 | 741 | 111.15 | 2008-06-23 08:04:50 |
| 164980 | 221181 | 1949 | 19670 | 1471 | 1906 | 1 | 1330 | 199.5 | 2008-03-21 07:59:51 |
| 159641 | 211179 | 44897 | 16652 | 7458 | 2128 | 1 | 1019 | 152.85 | 2008-10-29 02:02:15 |
| 73143 | 83439 | 5716 | 5727 | 7314 | 1903 | 1 | 248 | 37.2 | 2008-03-18 11:07:42 |
| 84778 | 96749 | 46608 | 32980 | 3883 | 1999 | 2 | 958 | 143.7 | 2008-06-22 12:13:31 |
| 171096 | 232929 | 43683 | 8536 | 8353 | 1870 | 1 | 929 | 139.35 | 2008-02-13 01:36:36 |
| 74212 | 84697 | 39809 | 15569 | 5525 | 2105 | 2 | 896 | 134.4 | 2008-10-06 11:47:50 |
| 158011 | 207556 | 25399 | 16881 | 232 | 2088 | 2 | 2526 | 378.9 | 2008-09-19 06:00:26 |
+---------+--------+----------+---------+---------+--------+---------+-----------+------------+---------------------+