# Source: https://docs.oxla.com/sql-reference/sql-clauses/limit.md

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

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

<CodeGroup>
  ```sql Version 1 theme={null}
  SELECT column_list
  FROM table_name
  ORDER BY sort_expression
  LIMIT row_count
  ```

  ```sql Version 2 theme={null}
  SELECT column_list
  FROM table_name
  ORDER BY sort_expression
  FETCH NEXT row_count ROWS ONLY
  ```
</CodeGroup>

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.

<Info>It is possible to have more than one table in the `FROM` clause.</Info>

* `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:

```sql  theme={null}
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:

```sql  theme={null}
SELECT * FROM comporders;
```

```sql  theme={null}
+-----------+------------+----------------------------------+-------------+----------+
| 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`:

```sql  theme={null}
SELECT order_id, prod_name, prod_price
FROM comporders
ORDER BY order_id
LIMIT 4;
```

The above query will give the following result:

```sql  theme={null}
+-----------+-------------------------------+-------------+
| 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:

```sql  theme={null}
SELECT * FROM comporders
ORDER BY prod_price DESC
LIMIT 5;
```

The result of the query is as follows:

```sql  theme={null}
+-----------+------------+----------------------------------+-------------+----------+
| 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:

```sql  theme={null}
SELECT * FROM comporders
ORDER BY prod_price ASC
LIMIT 5;
```

We will get the below output:

```sql  theme={null}
+-----------+------------+----------------------------------+-------------+----------+
| 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:

```sql  theme={null}
SELECT * FROM comporders
LIMIT 5 OFFSET 2;
```

After implementing the above command, we will get the below output:

```sql  theme={null}
+-----------+------------+----------------------------------+-------------+----------+
| 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.