How to retrieve a random row or multiple random rows in MySQL?

You can use RAND() method for this. To retrieve a random row, use the following syntax

SELECT *FROM yourTableName ORDER BY RAND() LIMIT yourIntegerNumber;

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table gettingRandomRow    -> (    -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> CustomerName varchar(100)    -> ); Query OK, 0 rows affected (0.45 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into gettingRandomRow(CustomerName) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into gettingRandomRow(CustomerName) values('Robert'); Query OK, 1 row affected (0.10 sec) mysql> insert into gettingRandomRow(CustomerName) values('Ramit'); Query OK, 1 row affected (0.15 sec) mysql> insert into gettingRandomRow(CustomerName) values('James'); Query OK, 1 row affected (0.11 sec) mysql> insert into gettingRandomRow(CustomerName) values('Jace'); Query OK, 1 row affected (0.12 sec) mysql> insert into gettingRandomRow(CustomerName) values('Mike'); Query OK, 1 row affected (0.15 sec) mysql> insert into gettingRandomRow(CustomerName) values('Sam'); Query OK, 1 row affected (0.17 sec) mysql> insert into gettingRandomRow(CustomerName) values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into gettingRandomRow(CustomerName) values('Carol'); Query OK, 1 row affected (0.11 sec) mysql> insert into gettingRandomRow(CustomerName) values('David'); Query OK, 1 row affected (0.16 sec) mysql> insert into gettingRandomRow(CustomerName) values('Maxwell'); Query OK, 1 row affected (0.14 sec) mysql> insert into gettingRandomRow(CustomerName) values('Larry'); Query OK, 1 row affected (0.13 sec) mysql> insert into gettingRandomRow(CustomerName) values('John'); Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from gettingRandomRow;

The following is the output

+------------+--------------+ | CustomerId | CustomerName | +------------+--------------+ |          1 | Chris        | |          2 | Robert       | |          3 | Ramit        | |          4 | James        | |          5 | Jace         | |          6 | Mike         | |          7 | Sam          | |          8 | Bob          | |          9 | Carol        | |         10 | David        | |         11 | Maxwell      | |         12 | Larry        | |         13 | John         | +------------+--------------+ 13 rows in set (0.00 sec)

Here is the query to get a random row or multiple random rows.

Case 1: The query is as follows −

mysql> select *from gettingRandomRow order by rand() LIMIT 1;

The following is the output

+------------+--------------+ | CustomerId | CustomerName | +------------+--------------+ |         13 | John         | +------------+--------------+ 1 row in set (0.00 sec)

Case 2: The query is as follows −

mysql> select *from gettingRandomRow order by rand() LIMIT 2;

The following is the output

+------------+--------------+ | CustomerId | CustomerName | +------------+--------------+ |          2 | Robert | |          3 | Ramit | +------------+--------------+ 2 rows in set (0.00 sec)

Case 3: The query is as follows −

mysql> select *from gettingRandomRow order by rand() LIMIT 2;

The following is the output

+------------+--------------+ | CustomerId | CustomerName | +------------+--------------+ | 4 | James | | 9 | Carol | +------------+--------------+ 2 rows in set (0.00 sec)