LIMIT

Overview

LIMIT is an optional clause that can be combined with SELECT statements used for retrieving records from one or more tables. It basically specifies the number of records a query should return after filtering the data.

Syntax

There are two versions available for the LIMIT clause syntax:

Version 1

SELECT column_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count

The parameters and arguments for specific version of the syntax are described 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.

Special Case

1. If the row_count value is NULL, the query will produce a similar outcome because it does not contain the LIMIT clause.
2. If row_count is zero, the statement will return an empty set.

Examples

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:

CREATE TABLE comporders  
(  
    order_id int,
    cust_name text,   
    prod_name text,   
    prod_price float,  
    status text 
);  

INSERT INTO comporders   
VALUES
(1002, 'Mike', 'Lenovo IdeaPad Flex 5', 600, 'PAID'),  
(1003, 'Sean', 'Acer Aspire 3', 450, 'PAID'),  
(1004, 'Victor', 'Microsoft Surface Laptop Go 2', 500, 'PENDING'),  
(1005, 'Lewis', 'Lenovo Duet 5i', 700, 'PAID'),  
(1006, 'David', 'Acer Swift 3', 640, 'PAID'),  
(1007, 'Meghan', 'Lenovo IdeaPad Duet 5 Chromebook', 750, 'PAID'),  
(1008, 'Harry', 'Apple iPad Air', 449, 'PENDING'),  
(1009, 'Steve', 'Microsoft Surface Go 3', 680, 'PENDING'),  
(1010, 'Omar', 'HP Victus 16', 800,'PAID');

To verify that the values have been inserted successfully, retrieve the result set using the command below:

SELECT * FROM comporders;

+-----------+------------+----------------------------------+-------------+----------+
| order_id  | cust_name  | prod_name                        | prod_price  | status   |
+-----------+------------+----------------------------------+-------------+----------+
| 1002      | Mike       | Lenovo IdeaPad Flex 5            | 600         | PAID     |  
| 1003      | Sean       | Acer Aspire 3                    | 450         | PAID     |
| 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  |
| 1009      | Steve      | Microsoft Surface Go 3           | 680         | PENDING  |
| 1010      | Omar       | HP Victus 16                     | 800         | PAID     |
+-----------+------------+----------------------------------+-------------+----------+

Case #1: Using LIMIT with the ORDER BY Expression

This example uses the LIMIT clause to get the first four orders sorted by order_id:

SELECT order_id, prod_name, prod_price
FROM comporders
ORDER BY order_id
LIMIT 4;

The above query will give the following result:

+-----------+-------------------------------+-------------+
| order_id  | prod_name                     | prod_price  |
+-----------+-------------------------------+-------------+
| 1002      | Lenovo IdeaPad Flex 5         | 600         | 
| 1003      | Acer Aspire 3                 | 450         |
| 1004      | Microsoft Surface Laptop Go 2 | 500         |
| 1005      | Lenovo Duet 5i                | 700         |
+-----------+-------------------------------+-------------+

Case #2: Using LIMIT with ASC/DESC

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:

SELECT * FROM comporders
ORDER BY prod_price DESC
LIMIT 5;

The result of the query is as follows:

+-----------+------------+----------------------------------+-------------+----------+
| 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.

The following query depicts the idea:

SELECT * FROM comporders
ORDER BY prod_price ASC
LIMIT 5;

We will get the below output:

+-----------+------------+----------------------------------+-------------+----------+
| 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:

SELECT * FROM comporders
LIMIT 5 OFFSET 2;

After implementing the above command, we will get the below 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.