LIMIT
LIMIT is an optional clause of the SELECT statement that constrains the number of rows the query returns.
The SELECT statement is used to retrieve records from one or more tables.
The following illustrates the syntax of the LIMIT clause:
The parameters and arguments from the syntax are explained below:
- column_list: The columns or calculations that you wish to retrieve.
- table_name: The tables that you want to retrieve records from.
It is possible to have more than one table in the FROM clause.
- ORDER BY : It is an expression used to order the results as you wish to return. The expression could be ascending (ASC) or descending (DESC).
- LIMIT row_count: It specifies a limited number of rows to be returned based on row_count.
- If the row_count value is NULL, the query will produce a similar outcome because it does not contain the LIMIT clause.
- If row_count is zero, the statement will return an empty set.
Letβs take some examples of the LIMIT clause.
Here we are creating one new table called comporders using the CREATE TABLE command and inserting some values into the table using the INSERT command:
To verify that the values have been inserted successfully, retrieve the result set using the command below:
This example uses the LIMIT clause to get the first four orders sorted by order_id:
The above query will give the following result:
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.
The following query depicts the idea:
The result of the query is as follows:
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.
The following query depicts the idea:
We will get the below output:
In this example, we will use LIMIT and OFFSET clauses to get 5 orders using the below query:
After implementing the above command, we will get the below output:
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.
ο»Ώ