- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we get randomly different set of rows or values each time from MySQL table?
When we use RAND() function along with both ORDER BY and LIMIT clause in a query, MySQL returns the different set of rows or values each time. To understand it considers a table ‘Employee’ having the following records −
mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | | 5 | Ram | 20000 | | 6 | Mohan | 30000 | | 7 | Aryan | NULL | | 8 | Vinay | NULL | +----+--------+--------+ 8 rows in set (0.00 sec)
Now, the query below will use RAND() function with both ORDER BY and LIMIT clause in a query return randomly different set of values or rows −
mysql> Select * from employee ORDER BY RAND() LIMIT 4; +----+-------+--------+ | ID | Name | Salary | +----+-------+--------+ | 5 | Ram | 20000 | | 4 | Aarav | 65000 | | 6 | Mohan | 30000 | | 8 | Vinay | NULL | +----+-------+--------+ 4 rows in set (0.00 sec) mysql> Select * from employee ORDER BY RAND() LIMIT 4; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 6 | Mohan | 30000 | | 8 | Vinay | NULL | | 2 | Rahul | 20000 | | 1 | Gaurav | 50000 | +----+--------+--------+ 4 rows in set (0.03 sec) mysql> Select * from employee ORDER BY RAND() LIMIT 4; +----+-------+--------+ | ID | Name | Salary | +----+-------+--------+ | 3 | Advik | 25000 | | 8 | Vinay | NULL | | 7 | Aryan | NULL | | 5 | Ram | 20000 | +----+-------+--------+ 4 rows in set (0.00 sec)
It can be observed from the above result sets that each time when we run the query, it returns a randomly different set of values or rows.
Advertisements