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)
Published on 16-Jan-2019 17:19:40
