1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL random rows: how to select random rows fast and fairly

Key takeaways

  • ORDER BY RANDOM() LIMIT n is 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 OFFSET took 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 1 or a random offset.
  • Analytics sample: TABLESAMPLE or 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Software Development

Python Abstract Class: ABC, abstractmethod and When to Use Them

Python abstract class explained: ABC and abstractmethod, abstract properties, shared logic with super, register, collections.abc and ABC vs Protocol, tested.

Read article →
Software Development

Python Subprocess Explained: run, Capture Output, Timeouts and Safety

Python subprocess explained with tested examples: run, capture_output, check, timeouts, input, environment, shell injection, encoding on Windows and Popen streaming.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
Software Development

URL Query Parameters: Encoding, URLSearchParams and Common Traps

URL query parameters explained: reading and building them with URLSearchParams and urllib, percent encoding, plus signs, repeated keys and nine…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Software Development

JavaScript BigInt Explained: Big Numbers, Division and JSON

JavaScript BigInt explained: the 2^53 limit, creating and converting BigInt, division, mixing types, JSON stringify and parse without losing digits,…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Databases & SQL

Gaps and Islands in SQL: Find Streaks and Missing Ranges

Gaps and islands in SQL: find login streaks and missing ranges with ROW_NUMBER, DENSE_RANK, LAG and LEAD, plus the duplicate…

Read article →
Databases & SQL

INSERT INTO SELECT in SQL: Copy Rows Between Tables Safely

INSERT INTO SELECT in SQL: copy rows between tables with chosen columns, joins and summaries, avoid duplicates on reruns, upsert,…

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →
Artificial Intelligence

K Means Clustering Explained: How It Works, With Python Code

K means clustering explained: the algorithm, a from-scratch NumPy version, choosing k with the elbow and silhouette methods, k-means++, scaling…

Read article →
DevOps

Git Log Explained: 15 Useful Commands and Pretty Formats

Git log explained with 15 tested commands: oneline, graph, filters by author, date, message and code, file history, stats, pretty…

Read article →
Test Automation

Pytest conftest.py Explained: Shared Fixtures, Hooks and Overrides

Pytest conftest.py explained with a tested suite: shared fixtures, scope and teardown, folder overrides, autouse, custom options, markers and useful…

Read article →
Software Testing

Usability Testing: Methods, Metrics, SUS Score and Checklist

Usability testing explained: methods, planning, writing tasks, running sessions, metrics, SUS scoring, how many users you need and a practical…

Read article →