LIMIT

PostgreSQL LIMIT is an optional clause of the SELECT statement that constrains the number of rows returned by the query.

Here’s the basic syntax of the LIMIT clause

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.

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

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

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

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

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

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

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

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.

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

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.