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.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 :
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 :
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 |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...