How to Randomly Select N Rows

  • by
Suppose there're 4 kinds of fruit displayed in a supermarket.
mysql> select * from fruits;
+----+--------+
| id | name   |
+----+--------+
|  1 | apple  |
|  2 | banana |
|  3 | cherry |
|  4 | mango  |
+----+--------+
4 rows in set (0.00 sec)

A mother asks you to select 2 fruits randomly for her from the list. In such case, you can use order by rand() combining with limit 2 to meet her requirements like this.
mysql> select * from fruits order by rand() limit 2;
+----+--------+
| id | name   |
+----+--------+
|  4 | mango  |
|  2 | banana |
+----+--------+
2 rows in set (0.00 sec)

Which means, the statement will sort the output based on their random value of each row generated by rand() function and then retrieve only the top 2 rows.

For ensuring the output is listed in sequence for her conveniences to pick up, you can using a subquery and order by the first column to achieve this:
mysql> select * from (select * from fruits order by rand() limit 2) t order by 1;
+----+--------+
| id | name   |
+----+--------+
|  2 | banana |
|  4 | mango  |
+----+--------+
2 rows in set (0.00 sec)

As simple as that.

Leave a Reply

Your email address will not be published. Required fields are marked *