RAND() Function in MySQL

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.9580191140603452	0.40613597483014313	0.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_id	Student_name	Student_Class	TotalExamGiven
1	Sayan	IX	8
2	Nitin	X	5
3	Aniket	XI	6
4	Abdur	X	7
5	Riya	IX	4
6	Jony	X	10
7	Deepak	X	7
8	Ankana	XII	5
9	Shreya	X	8

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_id	Student_name	Student_Class	TotalExamGiven
6	Jony	X	10
1	Sayan	IX	8
5	Riya	IX	4
2	Nitin	X	5
3	Aniket	XI	6
8	Ankana	XII	5
9	Shreya	X	8
4	Abdur	X	7
7	Deepak	X	7