SQL Random Sampling within Groups
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
Share your thoughts in the comments
Please Login to comment...