# LIMIT

PostgreSQL `LIMIT` is an optional clause of the [`SELECT`](https://neon.tech/postgresql/postgresql-tutorial/postgresql-select) statement that constrains the number of rows returned by the query.

Here’s the basic syntax of the `LIMIT` clause

```sql
SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count;
```

The statement returns `row_count` rows generated by the query. If the `row_count` is zero, the query returns an empty set. If the `row_count` is `NULL`, the query returns the same result set as it does not have the `LIMIT` clause.

If you want to skip a number of rows before returning the `row_count` rows, you can use `OFFSET` clause placed after the `LIMIT` clause.

```sql
SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count
OFFSET row_to_skip;
```

1. **Skipping Rows (OFFSET):** The `OFFSET` part of a query tells PostgreSQL to *skip* a certain number of rows at the start of the results. For example, if you skip 5 rows, the query will start showing results from the 6th row.
   * If you don’t want to skip any rows, just set `OFFSET` to 0, and it’s like there’s no skipping at all.
2. **Limiting Rows (LIMIT):** The `LIMIT` part tells PostgreSQL to only give you a specific number of rows, no more. For example, if you say `LIMIT 10`, you’ll get 10 rows in the result.
3. **Order of Operations:** PostgreSQL skips rows (using `OFFSET`) *before* it applies the `LIMIT`. So, it decides which rows to ignore before it picks the rows to show.
4. **Order Matters (ORDER BY):** PostgreSQL doesn’t automatically store rows in any particular order. That’s why, when you use `LIMIT`, you should always use `ORDER BY` to control which rows are picked. If you don’t, PostgreSQL will return rows in a random or unpredictable order.

**Example for clarity:**

* Query: `SELECT * FROM students ORDER BY grade DESC OFFSET 5 LIMIT 3;`
  * **Step 1:** Sort students by grades from highest to lowest.
  * **Step 2:** Skip the first 5 students.
  * **Step 3:** Show the next 3 students.

Without the `ORDER BY`, the rows shown could be in any order, which might not be what you want.

### Using PostgreSQL LIMIT to constrain the number of returned rows <a href="#id-1-using-postgresql-limit-to-constrain-the-number-of-returned-rows" id="id-1-using-postgresql-limit-to-constrain-the-number-of-returned-rows"></a>

The following statement uses the `LIMIT` clause to get the first five films sorted by `film_id`

```sql
SELECT film_id, title, release_year
FROM film
ORDER BY film_id
LIMIT 5;
```

<figure><img src="/files/3g9qg6MALf6wa5fNUiOt" alt=""><figcaption></figcaption></figure>

How it works.

* First, sort films by the `film_id` ascending order using the `ORDER BY film_id` clause.
* Second, take 5 films from the top using the `LIMIT 5` clause.

### Using the LIMIT clause with the OFFSET clause example <a href="#id-2-using-the-limit-clause-with-the-offset-clause-example" id="id-2-using-the-limit-clause-with-the-offset-clause-example"></a>

To retrieve 4 films starting from the fourth one ordered by `film_id`, you can use both `LIMIT` and `OFFSET` clauses as follows.

```sql
SELECT film_id, title, release_year
FROM film
ORDER BY film_id
LIMIT 4
OFFSET 3;
```

<figure><img src="/files/SziNATtjGhRMuBJoMKM0" alt=""><figcaption></figcaption></figure>

How it works.

* First, sort films by film id in ascending order.
* Second, skip the first three rows using the `OFFSET 3` clause.
* Second, take the next four rows using the `LIMIT 4` clause.

### Using LIMIT OFFSET to get top/bottom N rows <a href="#id-3-using-limit-offset-to-get-topbottom-n-rows" id="id-3-using-limit-offset-to-get-topbottom-n-rows"></a>

Typically, you often use the `LIMIT` clause to select rows with the highest or lowest values from a table. The following example uses the `LIMIT` clause to retrieve the top 10 most expensive films by rental rate.

```sql
SELECT film_id, title, rental_rate
FROM film
ORDER BY rental_rate DESC
LIMIT 10;
```

<figure><img src="/files/t1VzmsHvcxU3uqbQTxeE" alt=""><figcaption></figcaption></figure>

How it works.

* First, sort all the films by rental rates from high to low using the `ORDER BY rental_rate` clause.
* Second, take only 10 rows from the top using the `LIMIT 10` clause.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://courses.parottasalna.com/database-engineering/filtering-data/limit.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.