Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

RAND() Function in MySQL

  • Last Updated : 30 Sep, 2020

The RAND() function in MySQL is used to a return random floating-point value V in the range 0 <= V < 1.0. If we want to obtain a random integer R in the range i <= R < j, we have to use the expression :
FLOOR(i + RAND() * (j − i)).

Syntax :

RAND(N)

Parameter : This method accepts only one parameter.
N : If N is specified, it returns a repeatable sequence of random numbers. If no N is specified, it returns a completely random number. It is optional and it works as a seed value.
Returns : It returns a random floating number between 0 and 1.

Example-1 :
Obtaining a random value between 0 and 1 using RAND Function.

SELECT RAND() AS Random_Number;

Output :

Random_Number
0.6332025068189973


Example-2 :
Obtaining random value between 0 and 1 using RAND Function with seed value.

SELECT RAND(), RAND(5), RAND(5);

Output :

RAND()RAND(5)RAND(5)
0.95801911406034520.406135974830143130.40613597483014313

So, here we can see that, if we use the same seed value for generating the random number we will get the same random number as a result.


Example-3 :
Obtaining random value between in the range [ 5, 10 ) using RAND Function. Here, we will use the expression : FLOOR(i + RAND() * (j − i)) for generating the random number. Here, i will be 5 and j will be 10 .

SELECT FLOOR(5 + RAND()*(10-5)) AS Random_Number;

Output :

Random_Number
6


Example-4 :
Obtaining random value between in the range [ 5, 10 ] using RAND Function. Here, we will use the expression : FLOOR(i + RAND() * (j − i + 1)) for generating the random number. Here i will be 5 and j will be 10.

SELECT FLOOR(5 + RAND()*(10 - 5 + 1)) AS Random_Number;

Output :

Random_Number
10


Example-5 :
Using RAND Function to return rows from a category table by random order. To demonstrate create a table named Student.

CREATE TABLE Student(
          Student_id INT AUTO_INCREMENT,  
          Student_name VARCHAR(100) NOT NULL,
          Student_Class VARCHAR(20) NOT NULL,
          TotalExamGiven INT   NOT NULL,
          PRIMARY KEY(Student_id )
);

Now inserting some data to the Student table –

INSERT INTO  
          Student(Student_name, Student_Class, TotalExamGiven)
VALUES
          ('Sayan', 'IX', 8),
          ('Nitin', 'X', 5),
          ('Aniket', 'XI', 6),
          ('Abdur', 'X', 7),
          ('Riya', 'IX', 4),
          ('Jony', 'X', 10),
          ('Deepak', 'X', 7),
          ('Ankana', 'XII', 5),
          ('Shreya', 'X', 8);

To get all details about Student Table we will use –

SELECT * 
FROM Student;

Output :

Student_idStudent_nameStudent_ClassTotalExamGiven
1SayanIX8
2NitinX5
3AniketXI6
4AbdurX7
5RiyaIX4
6JonyX10
7DeepakX7
8AnkanaXII5
9ShreyaX8

So, we can see that all rows in the table are given in the right order. To return rows from the Student table by a random order we will use –

SELECT * 
FROM Student 
ORDER BY RAND();

Output :

Student_idStudent_nameStudent_ClassTotalExamGiven
6JonyX10
1SayanIX8
5RiyaIX4
2NitinX5
3AniketXI6
8AnkanaXII5
9ShreyaX8
4AbdurX7
7DeepakX7

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!