MySQL Select Random Records

Summary: in this tutorial, you will learn various techniques to select random records from a database table in MySQL.

Sometimes, you have to select random records from a table, for example:

• Select some random posts in a blog and display them in the sidebar.
• Select a random quote for displaying the “quote of the day” widget.
• Select random pictures in a gallery and use them as the featured pictures.

Selecting random records using ORDER BY RAND()

MySQL does not have any built-in statement to select random rows from a  table. In order to accomplish this, you use the RAND() function.

The following query selects a random row from a database table:

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

Let’s examine the query in more detail.

• The function RAND() generates a random value for each row in the table.
• The ORDER BY clause sorts all rows in the table by the random number generated by the RAND() function.
• The LIMITclause picks the first row in the result set sorted randomly.

If you want to select N random records from a database table, you need to change the LIMIT clause as follows:

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

See the following customers table from the sample database:

The following example selects five random customers from the customers table:

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

Try It Out

Notice that you may get a different result set because it is random.

This technique works very well with a small table. However, it will be slow for the big table because MySQL has to sort the entire table to select the random ones.

The speed of the query also depends on the number of rows in the table. The more rows the table has, the more time it takes to generate the random number for each row.

Selecting random records using an INNER JOIN clause

This technique requires that the table has an auto-increment primary key field and there is no gap in the sequence.

The following query generates 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)

We can join the table with the result set returned by the above query as follows:

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)

Using this technique, you must execute the query multiple times to get more than one random row because if you increase the limit, the query will only give you sequential rows that start from the randomly selected row.

The following query returns 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

In case the table has id column with the values that fall within a range 1..N and there is no gap in the range, you can use the following technique:

• First, select random numbers in the range 1..N.
• Second, pick the records based on the random numbers.

The following statement helps you accomplish this:

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)

Note that the user-defined variables are connection-specific. This means that this technique cannot be used with the connection pooling. In addition, the primary key must be integer type and its values must be in the sequence without gaps.

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