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;
Please Login to comment...