ক্রমবর্ধমান ক্রমে শেষ 20টি রেকর্ড নির্বাচন করতে, আপনি সাবকোয়েরি LIMIT ক্লজ ব্যবহার করতে পারেন। সিনট্যাক্স নিম্নরূপ
SELECT *FROM ( SELECT *FROM yourTableName ORDER BY yourColumnName desc limit 20 ) anyVariableName order by anyVariableName.yourColumnName;
উপরের সিনট্যাক্স বুঝতে, আসুন একটি টেবিল তৈরি করি। একটি টেবিল তৈরি করার প্রশ্নটি নিম্নরূপ
mysql> create table ProductInformation -> ( -> ProductId int, -> ProductName varchar(100), -> ProductPrice int -> ); Query OK, 0 rows affected (0.50 sec)
সন্নিবেশ কমান্ড ব্যবহার করে টেবিলে কিছু রেকর্ড সন্নিবেশ করান। প্রশ্নটি নিম্নরূপ
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)
সিলেক্ট স্টেটমেন্ট ব্যবহার করে টেবিল থেকে সমস্ত রেকর্ড প্রদর্শন করুন। প্রশ্নটি নিম্নরূপ
mysql> select *from ProductInformation;
নিম্নলিখিত আউটপুট
+-----------+-------------+--------------+ | 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)
এখানে সারণী থেকে শেষ 20টি রেকর্ড ঊর্ধ্বক্রম অনুসারে নির্বাচন করার জন্য প্রশ্ন রয়েছে
mysql> select *from -> ( -> select *from ProductInformation order by ProductId desc limit 20 -> ) t1 order by t1.ProductId asc;
নিম্নলিখিত আউটপুট
+-----------+-------------+--------------+ | 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)
আপনি যদি নিচের ক্রমে রেকর্ড চান, তাহলে desc ব্যবহার করুন। নিচের ক্রমানুসারে ফলাফল পেতে প্রশ্নটি নিম্নরূপ।
mysql> select *from -> ( -> select *from ProductInformation order by ProductId desc limit 20 -> ) t2 order by t2.ProductId desc;
নিম্নলিখিত আউটপুট
+-----------+-------------+--------------+ | 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)
