How to Retrieve a Random Row in SQL

1. Introduction

Retrieving a random row from a database table is a common requirement in SQL, especially when dealing with large datasets or when we need to sample data for analysis or testing purposes.

In this tutorial, we’ll cover the steps to retrieve a random row from a database table using the ORDER BY RANDOM() statement.

2. Illustrative Data Sample

Let’s refer to the sample Faculty table, which includes faculty members in departments like Computer Science and Mathematics, detailing their roles and employment statuses:

> SELECT * FROM Faculty;
 id  |      name      | national_id |      position       | start_date |  end_date  | department_id | active
-----+----------------+-------------+---------------------+------------+------------+---------------+--------
   1 | Anubha Gupta   |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2 | t
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |            |             1 | t
...
...
(7 rows)

In this context, the SELECT query enables us to display columns containing faculty details such as id, name, national_id, position, and more. Lastly, the entire table is sorted based on the id column.

3. Selecting a Random Row Using ORDER BY RANDOM()

Generally, the RANDOM() in SQL fetches random data from the table. Here, the query sorts all rows in the Faculty table randomly using ORDER BY RANDOM():

> SELECT * FROM Faculty ORDER BY RANDOM();
 id  |      name      | national_id |      position       | start_date |  end_date  | department_id | active
-----+----------------+-------------+---------------------+------------+------------+---------------+--------
 121 | Risa Sodi      |  1409239017 | Associate Professor | 2010-01-11 |            |             1 | t
 741 | Sophia Ker     |  2314437876 | Teaching Assistant  | 2022-08-11 |            |             4 | t
...
...
(7 rows)

3.1. Retrieving Only One Row From a Table

Here, the LIMIT clause restricts the result set to only one row after randomizing the rows with ORDER BY RANDOM(). This ensures that the query returns precisely one random row from the Faculty table:

> SELECT * FROM Faculty ORDER BY RANDOM() LIMIT 1;
 id  |     name     | national_id | position  | start_date |  end_date  | department_id | active
-----+--------------+-------------+-----------+------------+------------+---------------+--------
 601 | Sussie Smith |  1657230918 | Professor | 2019-01-11 | 2027-02-18 |             5 | t
(1 row)

> SELECT * FROM Faculty ORDER BY RANDOM() LIMIT 1;
 id  |    name    | national_id |      position      | start_date | end_date | department_id | active
-----+------------+-------------+--------------------+------------+----------+---------------+--------
 741 | Sophia Ker |  2314437876 | Teaching Assistant | 2022-08-11 |          |             4 | t
(1 row)

3.2. Retrieving Multiple Rows From a Table

Further, we can increase the number of rows returned by simply increasing the LIMIT number according to our requirements:

> SELECT * FROM Faculty ORDER BY RANDOM() LIMIT 2;
 id  |     name     | national_id |      position       | start_date |  end_date  | department_id | active
-----+--------------+-------------+---------------------+------------+------------+---------------+--------
 121 | Risa Sodi    |  1409239017 | Associate Professor | 2010-01-11 |            |             1 | t
   1 | Anubha Gupta |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2 | t
(2 rows)

> SELECT * FROM Faculty ORDER BY RANDOM() LIMIT 3;
 id  |      name      | national_id |      position       | start_date |  end_date  | department_id | active
-----+----------------+-------------+---------------------+------------+------------+---------------+--------
 601 | Sussie Smith   |  1657230918 | Professor           | 2019-01-11 | 2027-02-18 |             5 | t
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |            |             1 | t
 740 | Kira Wass      |  2314623876 | Teaching Assistant  | 2021-09-11 |            |             4 | t
(3 rows)

3.3. Fetching Random Rows in MSSQL and MySQL

Similarly, we’ll use the RAND() function in MySQL to select a random row from the table:

> SELECT * FROM Faculty ORDER BY RAND() LIMIT 1;

In contrast, MS SQL Server utilizes NEWID() for achieving randomness:

> SELECT TOP 1 * FROM Faculty ORDER BY NEWID();

Therefore, commands like RANDOM() in PostgreSQL, RAND() in MySQL, and NEWID() in MS SQL Server are used to randomize the row order, with LIMIT (MySQL) or TOP (MS SQL Server) used to restrict the result to one row.

4. Conclusion

In summary, retrieving a random row in SQL is crucial for data sampling and analysis, achievable through techniques like ORDER BY RANDOM() in PostgreSQL, ORDER BY RAND() in MySQL, and ORDER BY NEWID() in MS SQL Server. These commands effectively randomize row selection across diverse database platforms.