Select a random row from a database table

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

MySQL.sql

(sql)

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

PostgreSQL.sql

(sql)

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SQLServer.sql

(sql)

SELECT TOP 1 column FROM table
ORDER BY NEWID()

You may find that ORDER BY RAND() in MySQL or ORDER BY RANDOM() is PostgreSQL do not yield great performance on very large tables. Another approach you can take in that situation is to do two separate queries: a count and then an offset. Like this:

SELECT count(*) AS n FROM table

Then you pick a random number between 0 and n:

random.js

(javascript)

function randomInteger(min, max) {
  return Math.floor(Math.random() * (max - min + 1)) + min
}

and use it as the OFFSET value:

Alternative.sql

(sql)

SELECT column FROM table
LIMIT 1 OFFSET :randomValue

The LIMIT and OFFSET statements work in both MySQL and PostgreSQL, other database engines have similar functionality.

You'll have to pick that random value using your programming language of choice. This approach may not be that much faster so you should see how fast the original random query is compared to this approach.

References