> For the complete documentation index, see [llms.txt](https://courses.parottasalna.com/database-engineering/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://courses.parottasalna.com/database-engineering/filtering-data/limit.md).

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