SQL Reference ยท Scalar Functions
SQL RANDOM() function
Reference for random numbers in SQL: RANDOM() in SQLite and PostgreSQL, RAND() in MySQL, NEWID() in SQL Server, random sampling and dialect differences.
A random function returns an unpredictable number each time it is called. In SQL you use it to pick random rows, shuffle a result, generate test data and assign rows to random groups. The name and the kind of number it returns differ a lot between databases, and the standard does not define one.
In SQLite, RANDOM() returns a pseudo-random 64-bit signed integer (from about -9.2 quintillion to +9.2 quintillion). To get a smaller range, take a remainder and shift it.
SELECT RANDOM() AS big_integer;
Try it
Because the output changes on every run, the examples below show properties that are always the same.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
RANDOM()
| Part | Description |
|---|---|
| (no arguments) | Each call produces a new value. |
Returns: in SQLite an INTEGER from -9223372036854775808 to 9223372036854775807; elsewhere usually a double between 0 (inclusive) and 1 (exclusive).
Useful recipes in SQLite:
-- Integer from 1 to 6 SELECT ABS(RANDOM() % 6) + 1; -- Fraction from 0 up to (not including) 1 SELECT RANDOM() / 18446744073709551616.0 + 0.5; -- 8 random bytes (blob), e.g. for tokens SELECT HEX(RANDOMBLOB(8));
Dialect differences
| Database | Function | Returns |
|---|---|---|
| SQLite | RANDOM(), RANDOMBLOB(n) |
64-bit integer; random bytes |
| PostgreSQL | RANDOM() |
double precision, 0 <= x < 1 |
| MySQL | RAND(), RAND(seed) |
double, 0 <= x < 1 |
| SQL Server | RAND(), NEWID(), CRYPT_GEN_RANDOM(n) |
float; a random GUID; random bytes |
| Oracle | DBMS_RANDOM.VALUE, DBMS_RANDOM.VALUE(low, high) |
number in the range |
-- PostgreSQL: integer from 1 to 6 SELECT FLOOR(RANDOM() * 6) + 1; -- MySQL: random order SELECT * FROM products ORDER BY RAND() LIMIT 3; -- SQL Server: random order (RAND() would give every row the same value) SELECT TOP 3 * FROM products ORDER BY NEWID(); -- Oracle SELECT * FROM products ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 3 ROWS ONLY;
Usage notes
- Random sample:
ORDER BY RANDOM() LIMIT nis simple and fair, but it sorts the whole table, so it is slow on millions of rows. For big tables, filter on a random-looking indexed value or use a vendor feature such asTABLESAMPLE. - Called once per row. In SQLite and PostgreSQL
RANDOM()is evaluated for every row. In SQL ServerRAND()is evaluated once per query, which is whyNEWID()is used to shuffle. - Negative remainders.
RANDOM() % 6can be negative, so takeABS()first. In the extremely rare case thatRANDOM()is the smallest 64-bit integer,ABS()raises an integer overflow error. - Not cryptographically safe (except
RANDOMBLOBandCRYPT_GEN_RANDOM, which are meant for unpredictable bytes). Do not make passwords or tokens fromRAND(). - Repeatable results need a seed: MySQL's
RAND(42)repeats; SQLite and PostgreSQL'sRANDOM()cannot be seeded in a query (PostgreSQL hassetseed()). - Never use in constraints or indexes: the value changes on every read (a nondeterministic function).
- In
WHEREit filters a different random subset on each run:WHERE RANDOM() % 10 = 0keeps about one row in ten.
Related
ORDER BY,LIMIT and OFFSET,ABS(),MOD()- Lessons: Numeric functions, ORDER BY