PostgreSQL – LIMIT with OFFSET clause

The PostgreSQL LIMIT clause is used to get a subset of rows generated by a query. It is an optional clause of the SELECT statement. The LIMIT clause can be used with the OFFSET clause to skip a specific number of rows before returning the query for the LIMIT clause.

Syntax:SELECT * FROM table LIMIT n OFFSET m;

Let’s analyze the syntax above.

• The LIMIT clause returns a subset of “n” rows from the query result.
• The OFFSET clause placed after the LIMIT clause skips “m” number of rows before returning the result query.
• If “m” is Zero, then it acts as a normal LIMIT clause.

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link. Now, let’s look into a few examples. Example 1: Here we will query for 5 films starting from the seventh one ordered by “film_id” from the film table of our sample database.

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

Output: Example 2: Here we will query for 5 films starting from the seventh one ordered by “film_id” from the film table of our sample database in descending order of the film “title”.

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

Output:

My Personal Notes arrow_drop_up

Save