How to select random records from a database

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.

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;

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;

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();

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"}

• Rate it
• 
• 
• 
• 

Related Articles

Nothing yet..be the first to share wisdom.