SQL – SELECT RANDOM
RANDOM( ) in SQL is generally used to return a random row from a table present in the database. It has many applications in real life.
For example :
- There are a lot of employees in an organization. Suppose, if the event manager wants to mail any ten random employees then he/she can use the RANDOM( ) in SQL to get the Email Id of the ten random employees.
- It can also be used to display random questions during an online exam or MCQ from a pool of questions.
In this article, we are going to discuss how RANDOM( ) can be used using a sample table shown below.
Sample Input Table :
Customer Information | ||
---|---|---|
Customer ID | Customer Name | E-Mail Address |
1 | Srishti | abc@gmail.com |
2 | Rajdeep | def@gmail.com |
3 | Aman | xxx@gmail.com |
4 | Pooja | xyz@gmail.com |
BASIC SQL QUERY :
1. Creating a Database
CREATE DATABASE database_name;
2. Creating a Table
CREATE TABLE Table_name( col_1 TYPE col_1_constraint, col_2 TYPE col_2 constraint ..... ) col: Column name TYPE: Data type whether an integer, variable character, etc col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc
3. Inserting into a Table
INSERT INTO Table_name VALUES(val_1, val_2, val_3, ..........) val: Values in particular column
4. View The Table
SELECT * FROM Table_name
Output :
SQL QUERY FOR RANDOM :
1. MYSQL
SELECT col_1,col_2, ... FROM Table_Name ORDER BY RAND() col_1 : Column 1 col_2 : Column 2
The above query will return the entire table for the specific columns mentioned and the rows will be random and changing position every time we run the query. To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly.
SELECT col_1,col_2, ... FROM Table_Name ORDER BY RAND() LIMIT 1 col_1 : Column 1 col_2 : Column 2
2. PostgreSQL and SQLite
It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).
SELECT col_1,col_2, ... FROM Table_Name ORDER BY RAND() LIMIT 1 col_1 : Column 1 col_2 : Column 2
Output :
We can observe that the above queries return the rows randomly from all the set of rows in the table. The RANDOM( ) clause is beneficial when there are humongous records in the database.
Contact Us