Summary -

In this topic, we described about the ORDER BY RANDOM with detailed example.

There are various ways to get a random row from database. The syntax vary from one database to another database to get random row. ORDER BY RANDOM statement is used to fetch random row in postgre SQL database.

Syntax -

SELECT column1, column2, …, columnN
FROM table_name
ORDER BY RANDOM() LIMIT 1;
  • column1, column2, …, columnN - Specifies the column names from table.
  • table_name – Specifies the name of the table.

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch random row from table.

Requirement – Fetch random row from employee_details table. The query was as follows – 

SELECT * FROM employee_details
ORDER BY RANDOM() LIMIT 1;

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000

Scenario – Fetch random rows from table.

Requirement – Fetch 2 random rows from employee_details table. The query was as follows – 

SELECT * FROM employee_details
ORDER BY RANDOM() LIMIT 2;

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000