Select only 5 random rows in the last 50 entries With MySQL?

For this, use ORDER BY RAND() with subquery. Let us first create a table −

mysql> create table DemoTable1853
     (
     UserId int NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(UserId)
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1853 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
Query OK, 58 rows affected (0.00 sec)
Records: 58  Duplicates: 0  Warnings: 0

Display all records from the table using select statement −

mysql> select * from DemoTable1853;

This will produce the following output −

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
|     10 |
|     11 |
|     12 |
|     13 |
|     14 |
|     15 |
|     16 |
|     17 |
|     18 |
|     19 |
|     20 |
|     21 |
|     22 |
|     23 |
|     24 |
|     25 |
|     26 |
|     27 |
|     28 |
|     29 |
|     30 |
|     31 |
|     32 |
|     33 |
|     34 |
|     35 |
|     36 |
|     37 |
|     38 |
|     39 |
|     40 |
|     41 |
|     42 |
|     43 |
|     44 |
|     45 |
|     46 |
|     47 |
|     48 |
|     49 |
|     50 |
|     51 |
|     52 |
|     53 |
|     54 |
|     55 |
|     56 |
|     57 |
|     58 |
+--------+
58 rows in set (0.00 sec)

Here is the query to select only 5 random rows in the last 50 entries −

mysql> select tbl.*
     from (select tbl1.*
           from DemoTable1853 tbl1
           order by UserId DESC
           LIMIT 50
          ) tbl
     order by rand()
     limit 5;

This will produce the following output −

+--------+
| UserId |
+--------+
|     19 |
|     24 |
|     43 |
|     36 |
|     48 |
+--------+
5 rows in set (0.00 sec)

AmitDiwan

Updated on: 26-Dec-2019

269 Views

Kickstart Your Career

Get certified by completing the course

Get Started