Open In App

PostgreSQL – LIMIT with OFFSET clause

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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:


Last Updated : 23 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads