How to select random records from a database

Posted on 16th September 2019

In this tutorial you will learn how to retrieve a set of random records from a database table in MySQL, Oracle and Microsoft SQL Server. You'll also learn how to retrieve random documents from a collection in MongoDB database.

The following Employee table will be used for the example queries described here.

Table: Employee
EmpIDNameAddressEmail
1Mike Miller23 Workhaven Lane, 35200Mike.Miller@otg.com
2Kevin Stephens113 Warwick Way, 42399Kevin.Stephens@otg.com
3Phillip Gold28 London Road, 18743Phillip.Gold@otg.com
4Rob Smith57 Parkway close, 77948Rob.Smith@otg.com
5Vincent Gomez35 Wordsworth Ave, 45844Vincent.Gomez@otg.com

MySQL

You can use the RAND() function to select random records from a table in MySQL.

Syntax

SELECT columns FROM table ORDER BY RAND() LIMIT n;

The RAND() function generates a random number between 0 and 1 for each row in the table and the ORDER BY clause will order the rows by their random number. The LIMIT function limits the number of rows to return.

Example

SELECT * FROM employee ORDER BY RAND() LIMIT 2;
EmpIDNameAddressEmail
4Rob Smith57 Parkway close, 77948Rob.Smith@otg.com
3Phillip Gold28 London Road, 18743Phillip.Gold@otg.com

Oracle

In Oracle, the VALUE function in DBMS_RANDOM package returns a random number between 0 and 1 which can be combined with ORDER BY and FETCH clauses to return random rows.

Syntax

SELECT columns FROM table ORDER BY DBMS_RANDOM.value FETCH NEXT n ROWS ONLY;

Example

SELECT * FROM employee ORDER BY DBMS_RANDOM.value FETCH NEXT 2 ROWS ONLY;
EmpIDNameAddressEmail
3Phillip Gold28 London Road, 18743Phillip.Gold@otg.com
5Vincent Gomez35 Wordsworth Ave, 45844Vincent.Gomez@otg.com

MS SQL Server

In MS SQL Server, the NEWID() function assigns a unique random value to each row in the table and the ORDER BY clause sorts the records. The TOP clause limits the number of records.

Syntax

SELECT TOP n * FROM table ORDER BY NEWID();

Example

SELECT TOP 2 * FROM employee ORDER BY NEWID();
EmpIDNameAddressEmail
1Mike Miller23 Workhaven Lane, 35200Mike.Miller@otg.com
4Rob Smith57 Parkway close, 77948Rob.Smith@otg.com

MongoDB

MongoDB is a NoSQL database that stores data in the form of JSON documents inside a collection. To get random documents from a collection in MongoDB, use the $sample operator.

Syntax

{ $sample: { size: <positive integer> } }

Example

Consider a MongoDB collection named employee with the following documents.

{"_id":"5d7a5192948ab938987682e0","empid":1,"name":"Mike Miller","address":"23 Workhaven Lane, 35200","email":"Mike.Miller@otg.com"}
{"_id":"5d7f631333801a274c69c627","empid":2,"name":"Kevin Stephens","address":"113 Warwick Way, 42399","email":"Kevin.Stephens@otg.com"}
{"_id":"5d7f633c33801a274c69c628","empid":3,"name":"Phillip Gold","address":"28 London Road, 18743","email":"Phillip.Gold@otg.com"}
{"_id":"5d7f637933801a274c69c629","empid":4,"name":"Rob Smith","address":"57 Parkway close, 77948","email":"Rob.Smith@otg.com"}
{"_id":"5d7f63a033801a274c69c62a","empid":5,"name":"Vincent Gomez","address":"35 Wordsworth Ave, 45844","email":"Vincent.Gomez@otg.com"}

To retrieve two random documents from the above collection, run the command:

db.employee.aggregate(
   [ { $sample: { size: 2 } } ]
)
{"_id":"5d7f631333801a274c69c627","empid":2,"name":"Kevin Stephens","address":"113 Warwick Way, 42399","email":"Kevin.Stephens@otg.com"}
{"_id":"5d7f63a033801a274c69c62a","empid":5,"name":"Vincent Gomez","address":"35 Wordsworth Ave, 45844","email":"Vincent.Gomez@otg.com"}

Post a comment

Comments

Nothing yet..be the first to share wisdom.