Open In App

SQL – SELECT RANDOM

Last Updated : 21 Jul, 2021
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

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 :

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 :

Random Rows

RANDOM ROWS

RANDOM ROWS

RANDOM ROW

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
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
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
SQL Query to select Data from Tables Using Join and Where
The aim of this article is to make a simple program to Join two tables using Join and Where clause using MySQL. Below is the method to do the same using MySQL. The prerequisites of this article are MySQL and Apache Server on your computer are installed. What is a Query in SQL? A SQL query is a request passed for data/information from a table in a d
3 min read
SQL - SELECT DATE
In Microsoft SQL Server, SELECT DATE is used to get the data from the table related to the date, the default format of date is 'YYYY-MM-DD'. Syntax: SELECT * FROM table_name WHERE condition1, condition2,..; Now we will execute queries on SELECT DATE on database student in detail step-by-step: Step 1: Creating a database university: CREATE DATABASE
1 min read
SQL Query to Select all Records From Employee Table Where Name is Not Specified
Here, we are going to see how to find the names of the persons other than a person with a particular name SQL. In this article, we will be making use of the MSSQL Server as our database. For example, if the employee name is Pradeep you need to show the table of employees excluding that employee with the name Pradeep. So let us execute this query in
2 min read
SQL - SELECT NULL
The word NULL is used to describe a missing value in SQL. In a table, a NULL value is a value in a field that appears to be empty. A field with a NULL value is the same as one that has no value. It's important to grasp the difference between a NULL value and a zero value or a field of spaces. There are two possibilities: Where SQL is NULLSyntax: SE
2 min read
How to Select All Records from One Table That Do Not Exist in Another Table in SQL?
We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries. In this let us see How to select All Records from One Table That Do Not Exist in Another Table step-by-step. Creating a Database Use the below command to create a database named Geeks
2 min read
How to Select Words With Certain Values at the End of Word in SQL?
To select words with certain values at the end of the word In SQL, we can use pattern matching. A pattern matching allows users to search for certain patterns in the data. It is done using the LIKE operator in SQL. The query uses wildcard characters to match a pattern, Wildcard characters are case-sensitive. Some wildcard characters and their meani
2 min read
How to Exclude Records With Certain Values in SQL Select?
In this article, we will understand how to exclude some records having certain values from a table. For the purpose of demonstration, we will be creating a Participant table in a database called “GeeksForGeeksDatabase“. Step 1: Creating the DatabaseUse the below SQL statement to create a database called GeeksForGeeksDatabase. Query: CREATE DATABASE
3 min read
How to Select Group of Rows that Match All Items on a List in SQL Server?
In this article, we will see, how to select a group of rows that match all the items on a list in SQL Server. We can perform the above function by two methods. Those are: By using IN operatorBY performing JOIN using STRING_SPLIT methodIN operator: It allows you to specify a list of values in a WHERE clause. STRING_SPLIT(): this function can be used
2 min read
How to Select a Range of Letters in SQL?
In this article, we will see how to Select a Range of Letters in SQL using the LIKE clause. The LIKE clause is used for pattern matching in SQL using wildcard operators such as %, ^, etc. It has square brackets[] as one of the wildcard operators that is used to specify the range of characters for pattern matching. Let see these above mention functi
2 min read
How to Use SELECT In Order BY Specific Ids in SQL?
The order by the statement is used in SQL to sort the result set in ascending or descending by mentioning it in the suffix as DESC (for descending) and for ASC(for ascending). In this article, we will be doing order by on a database with some specified values of the column only. So let's start by creating a Database first. Step 1: Create a database
1 min read
How to Select Individual Columns in SQL?
In SQL, sometimes we require to select individual columns from a table. For this, we use a specific kind of query shown in the below demonstration. For this article, we will be using the Microsoft SQL Server as our database and Select keyword. Select is the most commonly used statement in SQL. The SELECT Statement in SQL is used to retrieve or fetc
2 min read
How to Use SELECT With Aggregate Functions in SQL?
In SQL, mainly five aggregate functions are provided. These help us to process the table data in the desired way. All these are illustrated below. For this article, we will be using the Microsoft SQL Server as our database. Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks. Query: CREATE DATABASE Gee
2 min read
How to Select Data Between Two Dates and Times in SQL Server?
In SQL, some transactions need to be extracted based on their completion times and dates. Here, the DATETIME2 data type in SQL to perform such operations. For this article, we will be using the Microsoft SQL Server as our database. Note: Here, we will use the two dates and times given in the query and separate them using the BETWEEN keyword. This i
2 min read
How to Select the First Row of Each GROUP BY in SQL?
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. To select the first row of each group in SQL, you can use the ' GROUP BY ' clause with the ' MIN ' or ' MAX ' aggregate function. SyntaxSELECT * FROM yourTable WHERE columnName = (SELECT MIN(columnName) FROM yourTable GROUP BY groupColumnNam
3 min read
SQL SELECT COUNT()
In the world of SQL(Structured Query Language), the 'SELECT' statement is a fundamental and powerful tool that allows you to retrieve and manipulate data stored in a database. In this article, we see what is SELECT in SQL, how the Count function works with the select command, the different arguments of the Select statement, the syntax of the Select
3 min read
Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT
In this article, we will be learning about how to do basic database operations using JDBC (Java Database Connectivity) API in Java programming language. These basic operations are INSERT, SELECT, UPDATE, and DELETE statements in SQL language. Although the target database system is Oracle Database, the same techniques can be applied to other databas
6 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records to the specified table. We can use this statement to add data directly to the table. We use the VALUES keyword along with the INSERT INTO statement. VALUES keyword is accompanied by column names in a specific order in which we want to insert values in them. SELECT statement is used
5 min read
SQL SELECT WHERE Field Contains Words
The SQL SELECT WHERE field Contains Words" in SQL is used to find data with specific words in it. It helps us to find patterns in text fields. One more way to do this is by using full-text search, which searches entire blocks of text to find what we need. In simple words, we can say SQL gives us ways to find data based on words or phrases in fields
4 min read
SET vs SELECT in SQL
In SQL, the use of SET and SELECT commands is fundamental for manipulating data and variables within a database. Understanding the differences between these two commands is crucial for performing various operations efficiently. This article explores the concepts of SET and SELECT in SQL, delving into their functionalities, and syntax, and providing
3 min read
How to SELECT DISTINCT on Multiple Columns in SQL?
In the world of databases, data duplication can lead to confusion and inefficiency. SQL provides a powerful tool, SELECT DISTINCT, to retrieve unique values from columns. However, when dealing with multiple columns, the approach becomes more detailed. In this article, We will explore How to SELECT DISTINCT on multiple columns in SQL by understandin
4 min read
How to Select Row With Max Value on a Column in SQL?
SQL is a powerful language for managing and handling relational databases. A common query requirement in relational databases is to get rows with the max value in a particular column. Here, we’ll look at different ways to do this, depending on different situations and database environments. In this article, we will see SQL queries to select row wit
5 min read
SQL SELECT FIRST
SQL SELECT FIRST retrieves the first/top row from the table and displays it in the results set. SELECT FIRST in SQLThe SELECT FIRST clause in SQL is used to fetch the first record from a column. This FIRST query can be used for Employee management systems, inventory management, and billing systems. Note: The SELECT FIRST clause is not universally s
2 min read
SQL - SELECT LAST
SQL (Structured Query Language), is the most popular standard language to work on databases. It is a domain-specific language that is mostly used to perform tons of operations which include creating a database, storing data in the form of tables, modifying, extract and lot more. There are different versions of SQL like MYSQL, PostgreSQL, Oracle, SQ
5 min read
Article Tags :
three90RightbarBannerImg