Select last 20 records ordered in ascending order in MySQL?

MySQL Database Training for Beginners

39 Lectures 5.5 hours

Simon Sez IT

More Detail

Python programming with MySQL database: from Scratch

152 Lectures 16 hours

Metla Sudha Sekhar

More Detail

Learn MySQL from scratch for Data Science and Analytics

87 Lectures 5.5 hours

Metla Sudha Sekhar

More Detail

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 22:30:24

• Related Questions & Answers
• Order MySQL records randomly and display name in Ascending order
• MySQL query to return 5 random records from last 20 records?
• How to select the last three rows of a table in ascending order with MySQL?
• Order date records and fetch the 2nd ordered record in MySQL
• How to order last 5 records by ID in MySQL
• MySQL command to order timestamp values in ascending order?
• Fetch ordered records after a specific limit in MySQL
• How can we sort MySQL output in ascending order?
• MySQL command to order timestamp values in ascending order without using TIMESTAMP()?
• MySQL SELECT last few days?
• Select with set order in MySQL
• Is it possible to divide records in both ascending and descending order in MySQL and display them alternatively?
• MySQL query to display records ordered by numeric difference?
• Order by last 3 chars in MySQL?
• How to select last row in MySQL?