Summary: in this tutorial, you will learn how to use the PostgreSQL LIMIT clause to get a subset of rows generated by a query.

Introduction to PostgreSQL LIMIT clause

PostgreSQL LIMIT is an optional clause of the SELECT a 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 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.

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.


Post a Comment