Select Random Records

– Show you various techniques to select random records from a database table.

Summary: This tutorial shows you how to select random records from a MySQL database table.

There are several common scenarios where you need to select random records from a table:

Display random blog posts in a sidebar
Show a random "quote of the day" in a widget
Feature random pictures in a gallery

Selecting random records using ORDER BY RAND()

Since MySQL has no built-in statement for selecting random rows, we use the RAND() function.

Here's a query that selects one random row:

SELECT *
FROM table_name
ORDER BY RAND()
LIMIT 1;Code language: SQL (Structured Query Language) (sql)

Here's how this query works:

The RAND() function generates a random value for each row
The ORDER BY clause sorts the rows by these random values
The LIMIT clause selects the first row from this random order

To select multiple random records, simply adjust the LIMIT value:

SELECT * FROM table_name
ORDER BY RAND()
LIMIT N;Code language: SQL (Structured Query Language) (sql)

Let's look at the customers table from the sample database:

This example selects five random customers:

SELECT
    customerNumber,
    customerName
FROM
    customers
ORDER BY RAND()
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

Try It Out

Each time you run this query, you'll get different results—that's the random part in action.

While this method works well for small tables, it becomes slow with larger ones because MySQL must sort the entire table.

Performance decreases as the table grows, since the random number generation happens for every row.

Selecting random records using an INNER JOIN clause

This alternative method requires a table with an auto-increment primary key field and no gaps in the sequence.

First, we generate a random number based on the primary key column:

SELECT
   ROUND(RAND() * ( SELECT MAX(id) FROM  table_name)) AS id;Code language: SQL (Structured Query Language) (sql)

Then we can join the table with this result:

SELECT t.*
FROM table_name AS t
INNER JOIN
    (SELECT ROUND(
       RAND() *
      (SELECT MAX(id) FROM table_NAME )) AS id
     ) AS x
WHERE
    t.id >= x.id
LIMIT 1;Code language: SQL (Structured Query Language) (sql)

Note that for multiple random rows, you'll need to execute this query multiple times. Increasing the LIMIT will only return sequential rows after the random starting point.

Here's how to get a random customer from the customers table:

SELECT
    t.customerNumber, t.customerName
FROM
    customers AS t
        JOIN
    (SELECT
        ROUND(RAND() * (SELECT
                    MAX(customerNumber)
                FROM
                    customers)) AS customerNumber
    ) AS x
WHERE
    t.customerNumber >= x.customerNumber
LIMIT 1;Code language: SQL (Structured Query Language) (sql)

Try It Out

Selecting random records using variables

For tables with an id column containing consecutive values from 1 to N, here's another approach:

First, select random numbers between 1 and N
Then select the records matching these numbers

Here's the query:

SELECT
    table. *
FROM
    (SELECT
        ROUND(RAND() * (SELECT
                    MAX(id)
                FROM
                    table)) random_num,
            @num:=@num + 1
    FROM
        (SELECT @num:=0) AS a, table
    LIMIT N) AS b,
    table AS t
WHERE
    b.random_num = t.id;Code language: SQL (Structured Query Language) (sql)

Remember that user-defined variables are connection-specific, making this method incompatible with connection pooling. Also, this requires an integer primary key with sequential values.

In this tutorial, we have shown you several techniques to select random records from a table.