Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT RANDOM() AS big_integer;

Try it

Because the output changes on every run, the examples below show properties that are always the same.

SQL Turning RANDOM() into a die roll and a fraction

Press Run (or Ctrl + Enter) to see the result here.

SQL Random sample of rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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:

SQL
-- 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
SQL
-- 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 n is 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 as TABLESAMPLE.
  • Called once per row. In SQLite and PostgreSQL RANDOM() is evaluated for every row. In SQL Server RAND() is evaluated once per query, which is why NEWID() is used to shuffle.
  • Negative remainders. RANDOM() % 6 can be negative, so take ABS() first. In the extremely rare case that RANDOM() is the smallest 64-bit integer, ABS() raises an integer overflow error.
  • Not cryptographically safe (except RANDOMBLOB and CRYPT_GEN_RANDOM, which are meant for unpredictable bytes). Do not make passwords or tokens from RAND().
  • Repeatable results need a seed: MySQL's RAND(42) repeats; SQLite and PostgreSQL's RANDOM() cannot be seeded in a query (PostgreSQL has setseed()).
  • Never use in constraints or indexes: the value changes on every read (a nondeterministic function).
  • In WHERE it filters a different random subset on each run: WHERE RANDOM() % 10 = 0 keeps about one row in ten.