Select last 20 records ordered in ascending order in MySQL?

To select last 20 records in ascending order, you can use subquery LIMIT clause. The syntax is as follows

SELECT *FROM
(
   SELECT *FROM yourTableName ORDER BY yourColumnName desc limit 20
)
anyVariableName order by anyVariableName.yourColumnName;

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table ProductInformation
   -> (
   -> ProductId int,
   -> ProductName varchar(100),
   -> ProductPrice int
   -> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command. The query is as follows

mysql> insert into ProductInformation values(101,'Product-1',200);
Query OK, 1 row affected (0.16 sec)

mysql> insert into ProductInformation values(102,'Product-2',300);
Query OK, 1 row affected (0.23 sec)

mysql> insert into ProductInformation values(103,'Product-3',700);
Query OK, 1 row affected (0.09 sec)

mysql> insert into ProductInformation values(104,'Product-4',100);
Query OK, 1 row affected (0.15 sec)

mysql> insert into ProductInformation values(105,'Product-5',1500);
Query OK, 1 row affected (0.18 sec)

mysql> insert into ProductInformation values(106,'Product-6',1200);
Query OK, 1 row affected (0.18 sec)

mysql> insert into ProductInformation values(107,'Product-7',1300);
Query OK, 1 row affected (0.17 sec)

mysql> insert into ProductInformation values(108,'Product-8',1600);
Query OK, 1 row affected (0.29 sec)

mysql> insert into ProductInformation values(109,'Product-9',1250);
Query OK, 1 row affected (0.15 sec)

mysql> insert into ProductInformation values(110,'Product-10',1900);
Query OK, 1 row affected (0.15 sec)

mysql> insert into ProductInformation values(111,'Product-11',1870);
Query OK, 1 row affected (0.13 sec)

mysql> insert into ProductInformation values(112,'Product-12',1876);
Query OK, 1 row affected (0.11 sec)

mysql> insert into ProductInformation values(113,'Product-13',1869);
Query OK, 1 row affected (0.19 sec)

mysql> insert into ProductInformation values(114,'Product-14',1456);
Query OK, 1 row affected (0.25 sec)

mysql> insert into ProductInformation values(115,'Product-15',1860);
Query OK, 1 row affected (0.16 sec)

mysql> insert into ProductInformation values(116,'Product-16',359);
Query OK, 1 row affected (0.21 sec)

mysql> insert into ProductInformation values(117,'Product-17',1667);
Query OK, 1 row affected (0.09 sec)

mysql> insert into ProductInformation values(118,'Product-18',1467);
Query OK, 1 row affected (0.11 sec)

mysql> insert into ProductInformation values(119,'Product-19',2134);
Query OK, 1 row affected (0.24 sec)

mysql> insert into ProductInformation values(120,'Product-20',3450);
Query OK, 1 row affected (0.10 sec)

mysql> insert into ProductInformation values(121,'Product-21',198);
Query OK, 1 row affected (0.22 sec)

mysql> insert into ProductInformation values(122,'Product-22',195);
Query OK, 1 row affected (0.21 sec)

mysql> insert into ProductInformation values(123,'Product-23',10000);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows

mysql> select *from ProductInformation;

The following is the output

+-----------+-------------+--------------+
| ProductId | ProductName | ProductPrice |
+-----------+-------------+--------------+
|       101 | Product-1   |          200 |
|       102 | Product-2   |          300 |
|       103 | Product-3   |          700 |
|       104 | Product-4   |          100 |
|       105 | Product-5   |         1500 |
|       106 | Product-6   |         1200 |
|       107 | Product-7   |         1300 |
|       108 | Product-8   |         1600 |
|       109 | Product-9   |         1250 |
|       110 | Product-10  |         1900 |
|       111 | Product-11  |         1870 |
|       112 | Product-12  |         1876 |
|       113 | Product-13  |         1869 |
|       114 | Product-14  |         1456 |
|       115 | Product-15  |         1860 |
|       116 | Product-16  |          359 |
|       117 | Product-17  |         1667 |
|       118 | Product-18  |         1467 |
|       119 | Product-19  |         2134 |
|       120 | Product-20  |         3450 |
|       121 | Product-21  |          198 |
|       122 | Product-22  |          195 |
|       123 | Product-23  |        10000 |
+-----------+-------------+--------------+
23 rows in set (0.00 sec)

Here is the query to select last 20 records from the table in ascending order

mysql> select *from
   -> (
   -> select *from ProductInformation order by ProductId desc limit 20
   -> ) t1 order by t1.ProductId asc;

The following is the output

+-----------+-------------+--------------+
| ProductId | ProductName | ProductPrice |
+-----------+-------------+--------------+
|       104 | Product-4   |          100 |
|       105 | Product-5   |         1500 |
|       106 | Product-6   |         1200 |
|       107 | Product-7   |         1300 |
|       108 | Product-8   |         1600 |
|       109 | Product-9   |         1250 |
|       110 | Product-10  |         1900 |
|       111 | Product-11  |         1870 |
|       112 | Product-12  |         1876 |
|       113 | Product-13  |         1869 |
|       114 | Product-14  |         1456 |
|       115 | Product-15  |         1860 |
|       116 | Product-16  |          359 |
|       117 | Product-17  |         1667 |
|       118 | Product-18  |         1467 |
|       119 | Product-19  |         2134 |
|       120 | Product-20  |         3450 |
|       121 | Product-21  |          198 |
|       122 | Product-22  |          195 |
|       123 | Product-23  |        10000 |
+-----------+-------------+--------------+
20 rows in set (0.00 sec)

If you want the records in descending order, then use desc. The query is as follows to get result in descending order.

mysql> select *from
   -> (
   -> select *from ProductInformation order by ProductId desc limit 20
   -> ) t2 order by t2.ProductId desc;

The following is the output

+-----------+-------------+--------------+
| ProductId | ProductName | ProductPrice |
+-----------+-------------+--------------+
|       123 | Product-23  |        10000 |
|       122 | Product-22  |          195 |
|       121 | Product-21  |          198 |
|       120 | Product-20  |         3450 |
|       119 | Product-19  |         2134 |
|       118 | Product-18  |         1467 |
|       117 | Product-17  |         1667 |
|       116 | Product-16  |          359 |
|       115 | Product-15  |         1860 |
|       114 | Product-14  |         1456 |
|       113 | Product-13  |         1869 |
|       112 | Product-12  |         1876 |
|       111 | Product-11  |         1870 |
|       110 | Product-10  |         1900 |
|       109 | Product-9   |         1250 |
|       108 | Product-8   |         1600 |
|       107 | Product-7   |         1300 |
|       106 | Product-6   |         1200 |
|       105 | Product-5   |         1500 |
|       104 | Product-4   |          100 |
+-----------+-------------+--------------+
20 rows in set (0.00 sec)

Chandu yadav

Updated on: 30-Jul-2019

326 Views

Kickstart Your Career

Get certified by completing the course

Get Started