Problem
How to select a random row using SQL?
Input
employee_id | first_name | last_name | salary |
---|---|---|---|
1 | John | Doe | 50000 |
2 | Jane | Smith | 60000 |
3 | Michael | Johnson | 55000 |
4 | Emily | Brown | 58000 |
5 | David | Davis | 62000 |
Try Hands-On: Fiddle
Create Input Table: Gist
Desired Output
employee_id | first_name | last_name | salary |
---|---|---|---|
5 | David | Davis | 62000 |
Solution 1:
Using generated Random number
SELECT * FROM employees
ORDER BY RAND()
LIMIT 1;
Explanation:
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.-
The RAND()
function generates a random value between 0 and 1 for each row, effectively randomizing the order of the results. -
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:
RAND() * (SELECT MAX(employee_id) FROM employees)
: This generates a random floating-point number between 0 and the maximum employee_id value.-
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. -
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.