Open In App

SQL Random Sampling within Groups

Improve
Improve
Like Article
Like
Save
Share
Report

RANDOM( ) in SQL is generally used to return a random row from a table present in the database. Here we will see, how to do Random Sampling within Groups in SQL using the RANDOM() function. For the purpose of demonstration, we will be creating a “employees” table in a database called “industry”.

Steps to SQL Random Sampling within Groups:

Step 1: Creating Database

Below SQL statement creates a database called industry.

Query:

CREATE DATABASE industry;

Step 2: Using the Database.

Below SQL statement switches database context to industry:

Query:

USE industry;

Step 3:Table Definition 

We have the following employees table in our industry database.

Query:

CREATE TABLE employees (
Emp_Id INTEGER PRIMARY KEY,
Emp_Name TEXT NOT NULL,
Emp_Email TEXT NOT NULL
);

Step 4: Inserting data into the table

Below SQL statement inserts data into the table employees:

Query:

INSERT INTO employees (Emp_Id, Emp_Name, Emp_Email)
 VALUES (1001, 'Hopper', 'hopper@itcomputers.com'),
 (1002, 'Lucas', 'lucas@itcomputers.com'),(1003, 'Max', 
 'max@itcomputers.com'), 
(1004, 'Robin', 'robin@itcomputers.com'),
(1005, 'Suzie', 'suzie@itcomputers.com'),
(1006, 'Will', 'will@itcomputers.com'),
(1007, 'Jane', 'jane@itcomputers.com'),
(1008, 'Mike', 'mike@itcomputers.com'),
(1009, 'Juliana', 'juliana@itcomputers.com'),
(1010, 'Lily', 'lily@itcomputers.com'),
(1011, 'Luke', 'luke@itcomputers.com');

Step 5: Below SQL statement displays the content in the table.

Query:

SELECT * FROM employees;

Output:

 

Now let’s find how to do Random Sampling within Groups in SQL using RAND() function. Below SQL statement is to display rows in random order using RAND() function:

Query:

SELECT * FROM table_name order by RANDOM();

In table_name mention your Table Name. Below SQL statement is to display the defined number of random rows from a table using RAND() function:

Query:

SELECT * FROM table_name order by RANDOM() LIMIT n;

In table_name mention your Table Name and in the place of ‘n’ give how many rows to be fetched. Limit defines the number of rows to be selected randomly from a table.

Example:

SQL query to return rows in the table ’employees’ in random order.

Query:

SELECT * FROM employees order by RANDOM();

Output:

 

SQL query to return eight (8) a random numbers of rows from the ’employees’ table:

Query:

SELECT * FROM employees order by RANDOM() LIMIT 8;

Output:

 

SQL query to return two rows from the ’employees’ table:

Query:

SELECT * FROM employees order by RANDOM() LIMIT 2;

Output:

 


Last Updated : 15 Jun, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads