LIMIT / OFFSET

LIMIT is an output modifier. Logically it is applied at the very end of the query. The LIMIT clause restricts the amount of rows fetched. The OFFSET clause indicates at which position to start reading the values, i.e., the first OFFSET values are ignored.

Note that while LIMIT can be used without an ORDER BY clause, the results might not be deterministic without the ORDER BY clause. This can still be useful, however, for example when you want to inspect a quick snapshot of the data.

An OFFSET over rows whose order is not guaranteed, that is, a query without ORDER BY whose rows pass through an operation that does not preserve their order, such as GROUP BY or a join, sends the client a WARNING notice: the rows it skips may differ between runs.

Examples

Select the first 5 rows from the addresses table:

SQL

SELECT *FROM addressesLIMIT 5;

 address         | city     | street_name | income | zip        | country | population | number1 | number2
-----------------+----------+-------------+--------+------------+---------+------------+---------+---------
 123 Spruce Blvd | Linden   | Spruce Blvd |  50000 | 11111      | NL      |        100 |       1 |      10
 111 Birch Ln    | Linden   | Birch Ln    |  60000 | 11111      | NL      |        100 |       2 |      20
 111 Birch Ln    | Lakeside | Birch Ln    |  70000 | 11111      | US      |         50 |       3 |      30
 111 Birch Ln    | Lakeside | Birch Ln    |  80000 | 11111-0001 | US      |         50 |       4 |      40
 5 Pond Road     | Mallard  | Pond Road   |  90000 | 22222      | UK      |         25 |       5 |      50

Select the 5 rows from the addresses table, starting at position 5 (i.e., ignoring the first 5 rows):

SQL

SELECT *FROM addressesLIMIT 5OFFSET 5;

 address     | city    | street_name | income | zip   | country | population | number1 | number2
-------------+---------+-------------+--------+-------+---------+------------+---------+---------
 9 Lake View | Mallard | Lake View   |  40000 | 33333 | UK      |         25 |       6 |      60

Select the top 5 cities with the highest population:

SQL

SELECT city, count(*) AS populationFROM addressesGROUP BY cityORDER BY population DESCLIMIT 5;

 city     | population
----------+------------
 Lakeside |          2
 Linden   |          2
 Mallard  |          2

Select 10% of the rows from the addresses table:

SQL

SELECT *FROM addressesLIMIT 10%;

 address   | city    | street_name | income | zip  | country | population | number1 | number2
-----------+---------+-------------+--------+------+---------+------------+---------+---------
 Address 0 | Linden  | Street 0    |  50000 | ZIP0 | NL      |        100 |       0 |       0
 Address 1 | Mallard | Street 1    |  51000 | ZIP1 | UK      |        101 |       1 |      10

Syntax

Syntax