August 3, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL LIMIT clause to get a subset of rows generated by a query.
Table of Contents
Introduction to PostgreSQL LIMIT
clause
PostgreSQL LIMIT
is an optional clause of the SELECT statement that constrains the number of rows returned by the query.
The following illustrates the 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 row_count
is zero, the query returns an empty set. In case row_count
is NULL
, the query returns the same result set as it does not have the LIMIT
clause.
In case you want to skip a number of rows before returning the row_count
rows, you use OFFSET
clause placed after the LIMIT
clause as the following statement:
SELECT select_list
FROM table_name
LIMIT row_count OFFSET row_to_skip;
The statement first skips row_to_skip
rows before returning row_count
rows generated by the query. If row_to_skip
is zero, the statement will work like it doesn’t have the OFFSET
clause.
Because a table may store rows in an unspecified order, when you use the LIMIT
clause, you should always use the ORDER BY clause to control the row order. If you don’t use the ORDER BY
clause, you may get a result set with the unspecified order of rows.
PostgreSQL LIMIT
examples
Let’s take some examples of using the PostgreSQL LIMIT
clause. We will use the film
table in the sample database for the demonstration.
1) Using PostgreSQL LIMIT to constrain the number of returned rows example
This example 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;
2) Using PostgreSQL LIMIT with OFFSET example
To retrieve 4 films starting from the fourth one ordered by film_id
, you use both LIMIT
and OFFSET
clauses as follows:
SELECT
film_id,
title,
release_year
FROM
film
ORDER BY
film_id
LIMIT 4 OFFSET 3;
3) Using PostgreSQL LIMIT OFFSSET 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.
For example, to get the top 10 most expensive films in terms of rental, you sort films by the rental rate in descending order and use the LIMIT
clause to get the first 10 films. The following query illustrates the idea:
SELECT
film_id,
title,
rental_rate
FROM
film
ORDER BY
rental_rate DESC
LIMIT 10;
The result of the query is as follows:
In this tutorial, you have learned how to use the PostgreSQL LIMIT OFFSET
clause to retrieve a subset of rows returned by a query.