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:
- Selecting some random posts in a blog and display them in the sidebar.
- Selecting a random quote for displaying “quote of the day” widget.
- Selecting random pictures in a gallery and use as the featured pictures.
MySQL select 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 BYclause sorts all rows in the table by the random number generated by theRAND()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)
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.
MySQL select random records using 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)
MySQL select 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. It 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.