How to select a random row using SQL?

Problem

How to select a random row using SQL?

Input

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

Solution 1:

Using generated Random number

SELECT * FROM employees
ORDER BY RAND()
LIMIT 1;

Explanation:

1. SELECT * FROM employees: This part of the query selects all columns from the “employees” table.
   ORDER BY RAND(): This part of the query orders the rows randomly.

2. The RAND() function generates a random value between 0 and 1 for each row, effectively randomizing the order of the results.

3. LIMIT 1: This part of the query limits the result to just one row, effectively returning a random row from the table.

When you run this query, it will return a single random row from the “employees” table with all the employee details, including employee_id, first_name, last_name, and salary.

Keep in mind that using ORDER BY RAND() can be slow for large tables because it has to generate a random number for each row and sort them.

If performance is a concern for very large tables, alternative methods like using a random offset or sampling with a subquery may be more efficient. However, for small to moderately sized tables, ORDER BY RAND() should work fine.

Solution 2:

More efficient / faster approach.

Using generated Random number and WHERE

SELECT * FROM employees
WHERE employee_id >= (SELECT FLOOR(RAND() * (SELECT MAX(employee_id) FROM employees)))
LIMIT 1;

Explanation:

1. RAND() * (SELECT MAX(employee_id) FROM employees): This generates a random floating-point number between 0 and the maximum employee_id value.

2. FLOOR(RAND() * (SELECT MAX(employee_id) FROM employees)): This rounds down the random number to the nearest whole number, ensuring it’s a valid employee_id.

3. employee_id >= ...: This condition ensures that the selected row has an employee_id greater than or equal to the random number, effectively creating a random offset within the range of employee_id values.

This approach avoids the overhead of sorting the entire table and is generally more efficient for large datasets.

Solution 3:

SELECT * FROM employees
WHERE employee_id = (
    SELECT employee_id
    FROM employees
    ORDER BY RAND()
    LIMIT 1
);

Explanation:

The subquery (SELECT employee_id FROM employees ORDER BY RAND() LIMIT 1) is used to fetch a single random employee_id from the “employees” table. Here’s how it works:

SELECT employee_id retrieves only the employee_id column from the table.
ORDER BY RAND() randomly orders the rows in the table.

This solution combines the efficiency of the subquery method with the simplicity of selecting all columns for the chosen random row. It’s a good choice for moderately sized tables.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to efficiently convert rows to columns in SQL?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?