SQL Random Rows: How to Select Random Rows Fast and Fairly
Selecting SQL random rows sounds easy with ORDER BY RANDOM(), until the table grows or the results turn out not to be random at all. Compare ORDER BY RANDOM, random offsets, random ID jumps, fraction filters and TABLESAMPLE on half a million rows, and see how one popular trick picked the same row 40 percent of the time.
ORDER BY RANDOM() LIMIT nis the simplest and fairest way to get SQL random rows, but it sorts the whole table.- On 300,000 rows it took 18.8 ms in SQLite; a random
OFFSETtook 1.6 ms. - The “random ID greater than or equal to” trick ran instantly but picked one row 7,937 times in 20,000 tries because of a gap in the IDs.
- For large tables, use database sampling such as
TABLESAMPLE, or a random offset with a count.
Show five random products on the home page, pick a random winner, sample 1 percent of users for a survey, grab test data that looks realistic. Selecting SQL random rows is a common request, and the first answer everyone finds works perfectly in development. In production, with millions of rows, it can become one of the slowest queries on the server, and the popular “fast” alternatives often stop being random.
We tested each method on SQLite 3.50.4 through Python, with timings and fairness checks. Syntax for other databases comes from their documentation and was not run here.
The basic syntax by database
| Database | Query |
|---|---|
| PostgreSQL, SQLite | SELECT * FROM t ORDER BY RANDOM() LIMIT 5; |
| MySQL, MariaDB | SELECT * FROM t ORDER BY RAND() LIMIT 5; |
| SQL Server | SELECT TOP 5 * FROM t ORDER BY NEWID(); |
| Oracle | SELECT * FROM t ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 5 ROWS ONLY; |
All of them do the same thing: give every row a random sort key, sort, and keep the first rows.
Our test table
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT);
-- 500,000 rows inserted, then ids 100000 to 299999 deleted
SELECT COUNT(*), MIN(id), MAX(id) FROM products;
-- (300000, 1, 500000)
We deliberately deleted a block of 200,000 IDs. Real tables have gaps from deleted rows, failed inserts and sequence caching, as our auto increment guide explains, and gaps are exactly what breaks some random-row tricks.
Method 1: ORDER BY RANDOM()
SELECT id FROM products ORDER BY RANDOM() LIMIT 5;
-- 18.8 ms, e.g. 353267, 85094, 54427
Correct and simple, and on 300,000 rows still fast enough for many uses. The cost grows with table size, because the database must generate a random number for every row and keep the best five. It cannot use an index, and on millions of wide rows, or under many concurrent requests, it becomes expensive. Selecting only the ID first and then fetching full rows by ID reduces the work. Our ORDER BY guide explains why sorting dominates the cost.
Is ORDER BY RANDOM() fair?
-- 10 rows, picked 10,000 times with ORDER BY RANDOM() LIMIT 1
counts: [933, 965, 982, 992, 999, 1016, 1016, 1021, 1024, 1052]
Each row should appear about 1,000 times, and every count landed within about 7 percent of that, normal random variation. ORDER BY RANDOM() gives every row an equal chance, which is why it is the reference the other methods should be judged against.
Method 2: random offset
SELECT id FROM products
LIMIT 1 OFFSET ABS(RANDOM()) % (SELECT COUNT(*) FROM products);
-- 1.6 ms, e.g. 485778
Pick a random position between 0 and the row count, then skip to it. Every row has an equal chance, gaps in IDs do not matter, and it was over ten times faster here. The database still steps over the skipped rows, so very large offsets get slower, and getting five rows means five queries or five offsets. In MySQL, compute the count and offset in your application, because LIMIT needs constants there. Paging internals are covered in our SQL pagination guide.
Method 3: random ID jump
SELECT id FROM products
WHERE id >= (ABS(RANDOM()) % (SELECT MAX(id) FROM products)) + 1
ORDER BY id LIMIT 1;
-- under 0.1 ms
Pick a random number between 1 and the highest ID, then take the first row at or above it. With an index on id, this is a single index lookup, by far the fastest method, which is why it appears in many tutorials. It is only fair if IDs have no gaps.
The hidden bias: our fairness test
-- 20,000 picks with the random ID jump
id 300000 picked 7937 times out of 20000
(fair share is about 0.07 times)
Row 300,000 sits right after the 200,000-ID gap. Every random number that landed inside the gap jumped forward to it, so a single product was chosen almost 40 percent of the time instead of about 0.0003 percent. Every row after a gap is over-represented in proportion to the size of the gap before it. If you use this method for “featured product” or “random reviewer”, the same few rows will dominate. Use it only when IDs are dense and stay dense, or when randomness does not need to be fair.
Tip: before trusting any random-row trick, run it thousands of times and count how often each row appears, exactly as we did here.
Method 4: random fraction filter
SELECT id FROM products WHERE ABS(RANDOM()) % 100000 < 1;
-- 16.2 ms, returned 2 rows (expected about 3)
Keep each row with a fixed probability, here one in 100,000. Every row has an equal chance, but the number of rows returned varies from run to run, and it still evaluates every row. It is best for sampling a percentage, for example 1 percent of users for analysis, rather than an exact count. Add LIMIT if you need at most N, accepting that rows near the start of the scan are slightly favoured.
Method 5: TABLESAMPLE
-- PostgreSQL
SELECT * FROM products TABLESAMPLE SYSTEM (1); -- about 1% of pages
SELECT * FROM products TABLESAMPLE BERNOULLI (1); -- about 1% of rows
-- SQL Server
SELECT * FROM products TABLESAMPLE (1 PERCENT);
Some databases sample at the storage level. SYSTEM picks whole data pages, which is extremely fast but clusters rows that are stored together; BERNOULLI picks individual rows, which is fairer but reads the whole table. PostgreSQL’s tsm_system_rows extension adds sampling a fixed number of rows. SQLite and MySQL do not support TABLESAMPLE; we did not run these queries.
Methods compared
| Method | Speed (300k rows) | Fair? | Exact count? |
|---|---|---|---|
| ORDER BY RANDOM() | 18.8 ms | Yes | Yes |
| Random OFFSET | 1.6 ms | Yes | One row per query |
| Random ID jump | under 0.1 ms | No, if IDs have gaps | One row per query |
| Fraction filter | 16.2 ms | Yes | No |
| TABLESAMPLE | Fast (not run) | Mostly | No |
Index the columns you filter and join on to keep every approach fast; see SQL indexes explained and query optimization.
Repeatable random samples
Random queries return different rows every run, which makes reports and tests hard to reproduce. For a stable sample, hash a key instead of calling a random function, for example keep rows whose hashed ID falls in the lowest 1 percent, or add a column with a random value assigned once and sample by ranges of it. In application code, a seeded generator, as shown in our Python random guide, gives the same picks every time.
Common uses and the best method
- “Random products” widget: cache a shuffled list of IDs for a few minutes and serve slices of it.
- Pick one winner fairly:
ORDER BY RANDOM() LIMIT 1or a random offset. - Analytics sample:
TABLESAMPLEor a fraction filter. - Test data: a seeded, repeatable sample; see test data generation.
Random rows per group
SELECT category, name FROM (
SELECT category, name,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY RANDOM()) AS rn
FROM products
) WHERE rn <= 2
ORDER BY category;
-- books item843, books item45, games item2449,
-- games item409, music item194, music item1502
A common request is “two random products from every category” for a home page or a balanced test sample. Plain ORDER BY RANDOM() LIMIT 6 cannot guarantee that, because one category might take all six slots. The window function version shuffles rows inside each category, numbers them, and keeps the first two of each. We ran it on a 3,000 row table with three categories in SQLite 3.50.4 and got exactly two rows per category every time.
To check it was fair, we ran the query 3,000 times. That is 18,000 picks spread over 3,000 products, so each product should appear about six times. 2,992 of the 3,000 products were picked at least once, with counts from 1 to 16, which is the spread you expect from pure chance, not a bias. Like plain ORDER BY RANDOM(), it still sorts every row of each group, so it suits tables of thousands to low millions of rows rather than billions. Our SQL window functions tutorial explains PARTITION BY and ROW_NUMBER in depth.
Final thoughts
SQL random rows come down to a trade-off between speed and fairness. ORDER BY RANDOM() is fair and fine for small and medium tables; a random offset keeps fairness with much less work; sampling clauses scale to huge tables. The random ID jump is fastest, but gaps make it biased, as our test showed with one row taking 40 percent of the picks. Measure both the time and the distribution before you choose.
Frequently asked questions
How do I select random rows in SQL?
Use ORDER BY RANDOM() with LIMIT in PostgreSQL and SQLite, ORDER BY RAND() in MySQL, or ORDER BY NEWID() with TOP in SQL Server.
Why is ORDER BY RAND slow on large tables?
It assigns a random value to every row and then sorts them all, so the work grows with the size of the table and no index can help.
What is the fastest way to get a random row?
Jumping to a random ID is fastest, but it is biased when IDs have gaps. A random OFFSET based on the row count is fast and fair for one row at a time.
How do I get a random sample percentage in SQL?
Use TABLESAMPLE in PostgreSQL or SQL Server, or filter each row with a random number below your chosen fraction. The row count will vary between runs.
How do I get the same random rows every time?
Base the selection on a hash of a stable key, or on a random value stored once in a column, instead of calling a random function in each query.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.