close
The Wayback Machine - https://web.archive.org/web/20240710003246/https://www.geeksforgeeks.org/sql-select-random/
Open In App

SQL – SELECT RANDOM

Last Updated : 21 Jul, 2021
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. It has many applications in real life. 

For example : 

  1. 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.
  2. 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 :

BERJAYA

Customer Table

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 :

BERJAYA

Random Rows

BERJAYA

RANDOM ROWS

BERJAYA

RANDOM ROWS

BERJAYA

RANDOM ROW

BERJAYA

RANDOM ROW

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.


Previous Article
Next Article

Similar Reads

SQL - SELECT from Multiple Tables with MS SQL Server
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n
3 min read
How to SQL Select from Stored Procedure using SQL Server?
There may be situations in SQL Server where you need to use a stored procedure to get data from a SQL query. For direct data selection from a stored procedure within a query, SQL Server offers options like OPENQUERY and OPENROWSET. The usual way is running the stored procedure independently and then querying the outcomes. The idea of utilizing SQL
3 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
SQL - SELECT AS
In SQL, we have databases that contain tables where our data is present. If we want to select some data from this table then we make use of the SELECT clause with FROM clause specifying the table. We can apply some conditions to our selections using the WHERE clause. Now some cases might be there where we are required to change the name of data sel
2 min read
Select statement in MS SQL Server
In this article, the most basic statement used in SQL is being discussed. Basic statement: select There are large amounts of data available everywhere. The data is stored in a sequential order in form of a table. Each table consists of a row which represents a unique record and column represents a field. Schemas are used to arrange the tables in a
2 min read
Select top in MS SQL Server
Prerequisite - Select in MS SQL Server Suppose that a user wants to extract the top students from the whole institution but has to use some complex queries to extract the data. To avoid complexity, the user can use 'Select Top'. 'Select Top' extracts the limited number of rows. This results in accurate data along with less time consumption. Syntax
2 min read
Insert Into Select statement in MS SQL Server
Prerequisites - Insert statement in MS SQL Server, Select statement in MS SQL Server Consider a university database. There are two tables namely Student and Marks. In case, the marks of a few students has to be transferred from the marks table to the marks table, there are many ways to do so. One can use subqueries (query nested in another query) b
2 min read
SELECT data from Multiple Tables in SQL
The statement is used to retrieve the fields from multiple tables and with the help of JOIN operations we easily fetch the records from multiple tables, Generally JOINS are used when there are common records between two tables. In this article, we will look into various types of JOIN that are used in SQL. SQL JoinsSQL joins combine two or more tabl
3 min read
SQL | Query to select NAME from table using different options
Let us consider the following table named "Geeks" : G_ID FIRSTNAME LASTNAME DEPARTMENT 1 Mohan Arora DBA 2 Nisha Verma Admin 3 Vishal Gupta DBA 4 Amita Singh Writer 5 Vishal Diwan Admin 6 Vinod Diwan Review 7 Sheetal Kumar Review 8 Geeta Chauhan Admin 9 Mona Mishra Writer SQL query to write “FIRSTNAME” from Geeks table using the alias name as NAME.
2 min read
Article Tags :