Suppose we have a use case to order the number of rows in MySQL randomly. How can we do it? Assume we have a list of 5 items you want to display, but these items are randomly chosen items. In MySQL, you use the rand() function in the ORDER BY clause.
Software Environment For MySQL
This post uses the following items. To create a MySQL Docker container, please follow the Docker Compose for MySQL For Local Development.
- Windows 10
- MySQL 8 Docker image
The SQL Statement With Random Rows
Suppose we have the following MySQL table with only five rows. But we have a requirement to display random rows coming from MySQL.
1 2 3 4 5 6 | |id|person_name|age| |1 |Karl |28 | |2 |Pete |11 | |3 |Andrew |15 | |4 |Steve |19 | |5 |Xavier |50 | |
We can display the rows randomly by using and tweaking the ORDER BY clause. Moreover, we use the rand function with the ORDER BY clause.
1 | SELECT * FROM persons ORDER BY RAND() |
Every time we run the MySQL command, we get the same rows but in random order. For example, we get the following on the first run.
1 2 3 4 5 6 | |id|person_name|age| |5 |Xavier |50 | |1 |Karl |28 | |4 |Steve |19 | |2 |Pete |11 | |3 |Andrew |15 | |
Next, we get the following on the second run.
1 2 3 4 5 6 | |id|person_name|age| |5 |Xavier |50 | |3 |Andrew |15 | |4 |Steve |19 | |1 |Karl |28 | |2 |Pete |11 | |
Then, on the third run, we get yet another different result.
1 2 3 4 5 6 | |id|person_name|age| |3 |Andrew |15 | |5 |Xavier |50 | |4 |Steve |19 | |2 |Pete |11 | |1 |Karl |28 | |
Note that when others run the same MySQL command, their random rows may vary from the results of this post.
