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)
Published on 03-Sep-2019 12:27:54
