website logo
โŒ˜K
๐Ÿ Homepage
๐Ÿ‘‹Introduction
Key Concepts & Architecture
๐Ÿš€Getting Started
๐Ÿ‘จโ€๐Ÿ’ปSQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
๐ŸšจError Handling
๐Ÿ†šDifferences Between Oxla vs. PostgreSQL
Docs powered byย archbeeย 

LIMIT

18min

In this article, we will understand the LIMIT clause, which is used to get a subset of rows generated by a query.

Overview

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.

Syntax

The following illustrates the syntax of the LIMIT clause:

Syntax
|
SELECT column_list  
FROM table_name  
ORDER BY sort_expression  
LIMIT row_count
๏ปฟ

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.

๐Ÿ’ก 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 a table
|
CREATE TABLE comporders  
(  
    order_id int,
    cust_name string,   
    prod_name string,   
    prod_price float,  
    status string  
);  

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:

Display the table
|
SELECT * FROM comporders;
๏ปฟ
Output
|
+-----------+------------+----------------------------------+-------------+----------+
| 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:

Limit query
|
SELECT order_id, prod_name, prod_price
FROM comporders
ORDER BY order_id
LIMIT 4;
๏ปฟ

The above query will give the following result:

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

Limit query
|
SELECT * FROM comporders
ORDER BY prod_price DESC
LIMIT 5;
๏ปฟ

The result of the query is as follows:

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

Limit query
|
SELECT * FROM comporders
ORDER BY prod_price ASC
LIMIT 5;
๏ปฟ

We will get the below output:

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:

Limit query
|
SELECT * FROM comporders
LIMIT 5 OFFSET 2;
๏ปฟ

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.

๏ปฟ

Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
ORDER BY
NEXT
OFFSET
Docs powered byย archbeeย 
TABLE OF CONTENTS
Overview
Syntax
๐Ÿ’ก Special Case:
Examples
Case #1: Using LIMIT with the ORDER BY Expression
Case #2: Using LIMIT with ASC/DESC
#Case 3: Using LIMIT with OFFSET

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




ยฉ2022 Oxla