SQL RAND / RANDOM: Generate Random Numbers and Select Random Rows

Overview

Generate random numbers or select random rows from a table. Useful for sampling, testing, and randomized selection.

MySQL

-- Random decimal 0-1:
SELECT RAND();               -- 0.7234...

-- Random integer in range (1-100):
SELECT FLOOR(RAND() * 100) + 1;

-- Random row:
SELECT * FROM users ORDER BY RAND() LIMIT 1;

-- Random 10 rows:
SELECT * FROM users ORDER BY RAND() LIMIT 10;

-- Reproducible random (seed):
SELECT RAND(42);  -- always same result with same seed

PostgreSQL

-- Random decimal 0-1:
SELECT RANDOM();              -- 0.7234...

-- Random integer in range (1-100):
SELECT FLOOR(RANDOM() * 100 + 1)::INT;

-- Random row:
SELECT * FROM users ORDER BY RANDOM() LIMIT 1;

-- Faster for large tables — TABLESAMPLE:
SELECT * FROM users TABLESAMPLE SYSTEM(1);  -- ~1% of rows
SELECT * FROM users TABLESAMPLE BERNOULLI(5); -- ~5% of rows

-- Set seed for reproducibility:
SELECT SETSEED(0.42);
SELECT RANDOM();  -- reproducible

SQL Server

-- Random decimal 0-1:
SELECT RAND();                -- 0.7234...

-- Random integer (1-100):
SELECT FLOOR(RAND() * 100) + 1;

-- Random row (NEWID is better for per-row randomness):
SELECT TOP 1 * FROM users ORDER BY NEWID();

-- TABLESAMPLE:
SELECT * FROM users TABLESAMPLE (10 PERCENT);

-- Random per-row (RAND() is same for all rows in a query):
SELECT *, ABS(CHECKSUM(NEWID())) % 100 AS random_num FROM users;

Oracle

-- DBMS_RANDOM.VALUE:
SELECT DBMS_RANDOM.VALUE FROM DUAL;          -- 0 to 1
SELECT DBMS_RANDOM.VALUE(1, 100) FROM DUAL;  -- 1 to 100

-- Random row:
SELECT * FROM (
  SELECT * FROM users ORDER BY DBMS_RANDOM.VALUE
) WHERE ROWNUM = 1;

-- SAMPLE clause:
SELECT * FROM users SAMPLE(10);  -- ~10% of rows

Skip the Syntax Lookup

Instead of memorizing RAND / RANDOM syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions