You can use the LIMIT clause to select rows with the highest or lowest values from a table.
1) To get the top 5 most expensive orders, you sort orders by the product price in descending order (DESC) and use the LIMIT clause to get the first 5 orders.
+-----------+------------+----------------------------------+-------------+----------+| order_id | cust_name | prod_name | prod_price |status|+-----------+------------+----------------------------------+-------------+----------+|1010| Omar | HP Victus 16|800| PAID ||1007| Meghan | Lenovo IdeaPad Duet 5 Chromebook |750| PAID ||1005| Lewis | Lenovo Duet 5i |700| PENDING ||1009| Steve | Microsoft Surface Go 3|680| PENDING ||1006| David | Acer Swift 3|640| PAID |+-----------+------------+----------------------------------+-------------+----------+
๏ปฟ
2) We will fetch the top 5 cheapest orders this time. You sort orders by the product price in ascending order (ASC) and use the LIMIT clause to get the first 5 orders.
+-----------+------------+----------------------------------+-------------+----------+| order_id | cust_name | prod_name | prod_price |status|+-----------+------------+----------------------------------+-------------+----------+|1008| Harry | Apple iPad Air |449| PENDING ||1003| Sean | Acer Aspire 3|450| PAID ||1004| Victor | Microsoft Surface Laptop Go 2|500| PENDING ||1002| Mike | Lenovo IdeaPad Flex 5|600| PAID ||1006| David | Acer Swift 3|640| PAID |+-----------+------------+----------------------------------+-------------+----------+
๏ปฟ
#Case 3: Using LIMIT with OFFSET
In this example, we will use LIMIT and OFFSET clauses to get 5 orders using the below query:
Limit query
|
SELECT*FROM comporders
LIMIT5OFFSET2;
๏ปฟ
After implementing the above command, we will get the below output:
Output
|
+-----------+------------+----------------------------------+-------------+----------+| order_id | cust_name | prod_name | prod_price |status|+-----------+------------+----------------------------------+-------------+----------+|1004| Victor | Microsoft Surface Laptop Go 2|500| PENDING ||1005| Lewis | Lenovo Duet 5i |700| PENDING ||1006| David | Acer Swift 3|640| PAID ||1007| Meghan | Lenovo IdeaPad Duet 5 Chromebook |750| PAID ||1008| Harry | Apple iPad Air |449| PENDING |+-----------+------------+----------------------------------+-------------+----------+
๏ปฟ
The result above shows that:
The orders with order_id = 1002 & 1003 aren't displayed because we put the OFFSET value with 2. So the first 2 lines are ignored.
The orders with order_id = 1009 & 1010 aren't displayed because the LIMIT value is 5, which will display only 5 rows.