MySQL query to return 5 random records from last 20 records?

For this, you need to use ORDER BY to order records. With that use RAND() to get random records and LIMIT 5 since we want to display only 5 random records.

Let us first create a table −

mysql> create table DemoTable773 (StudentId int);
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable773 values(100);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable773 values(200);
Query OK, 1 row affected (0.87 sec)
mysql> insert into DemoTable773 values(300);
Query OK, 1 row affected (1.59 sec)
mysql> insert into DemoTable773 values(400);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable773 values(500);
Query OK, 1 row affected (0.29 sec)
mysql> insert into DemoTable773 values(1);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable773 values(2);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable773 values(3);
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable773 values(4);
Query OK, 1 row affected (0.73 sec)
mysql> insert into DemoTable773 values(5);
Query OK, 1 row affected (0.77 sec)
mysql> insert into DemoTable773 values(6);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable773 values(7);
Query OK, 1 row affected (0.24 sec)
mysql> insert into DemoTable773 values(8);
Query OK, 1 row affected (0.50 sec)
mysql> insert into DemoTable773 values(9);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable773 values(10);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable773 values(90);
Query OK, 1 row affected (0.26 sec)
mysql> insert into DemoTable773 values(91);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable773 values(92);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable773 values(93);
Query OK, 1 row affected (0.33 sec)
mysql> insert into DemoTable773 values(94);
Query OK, 1 row affected (0.38 sec)
mysql> insert into DemoTable773 values(95);
Query OK, 1 row affected (0.34 sec)
mysql> insert into DemoTable773 values(96);
Query OK, 1 row affected (0.24 sec)
mysql> insert into DemoTable773 values(97);
Query OK, 1 row affected (0.27 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable773;

This will produce the following output -

+-----------+
| StudentId |
+-----------+
|       100 |
|       200 |
|       300 |
|       400 |
|       500 |
|         1 |
|         2 |
|         3 |
|         4 |
|         5 |
|         6 |
|         7 |
|         8 |
|         9 |
|        10 |
|        90 |
|        91 |
|        92 |
|        93 |
|        94 |
|        95 |
|        96 |
|        97 |
+-----------+
23 rows in set (0.00 sec)

Following is the query to return 5 random records from

the last 20 records −

mysql> select *from (
   select * from DemoTable773
   order by StudentId desc limit 20
) AS RANDOM_OUTPUT
order by rand()
limit 5;

This will produce the following output -

+-----------+
| StudentId |
+-----------+
|        95 |
|         4 |
|        10 |
|         7 |
|       300 |
+-----------+
5 rows in set (0.51 sec)

AmitDiwan

Published on 03-Sep-2019 08:57:54

Previous Page Print Page