LIMIT | FETCH FIRST

Both LIMIT and FETCH FIRST can limit the number of rows in the result set. LIMIT and OFFSET can be used together for pagination query.

LIMIT

LIMIT { count | ALL }

The following query limits the number of returned rows to 5:

SELECT * FROM fruit LIMIT 5;

LIMIT ALL has the same function as omitting LIMIT.

FETCH FIRST

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES

FETCH FIRST supports the FIRST or NEXT keywords and the ROW or ROWS keywords. These keywords are equivalent and do not affect the execution of queries.

If FETCH FIRST is not specified, the default value 1 is used.

SELECT orderdate FROM orders FETCH FIRST ROW ONLY;
  orderdate   
--------------
 2020-11-11 

SELECT *  FROM new_orders FETCH FIRST 2 ROW ONLY;
   orderkey   | orderstatus | totalprice |  orderdate   
--------------|-------------|------------|--------------
 202011181113 | online      |     9527.0 | 2020-11-11 
 202011181114 | online      |      666.0 | 2020-11-11 
(2 rows)

If OFFSET is used, LIMIT or FETCH FIRST applies to the result set after OFFSET:

SELECT * FROM (VALUES 5, 2, 4, 1, 3) t(x) ORDER BY x OFFSET 2  FETCH FIRST ROW ONLY;
 x 
---
 3 
(1 row)

For the FETCH FIRST clause, the ONLY or WITH TIES parameter controls which rows are contained in the result set.
If the ONLY parameter is specified, the result set is limited to the first several rows that contain the number of parameters.

If WITH TIES is specified, the ORDER BY clause must be used. The result set contains the basic result set of the first several rows that meet the conditions and additional rows. These extra return rows are the same as the parameters of ORDER BY in the last row of the basic result set:
```
CREATE TABLE nation (name varchar,  regionkey integer);

insert into nation values ('ETHIOPIA',0),('MOROCCO',0),('ETHIOPIA',2),('KENYA',2),('ALGERIA',0),('MOZAMBIQUE',0);

--Return all records whose regionkey is the same as the first record.
SELECT name, regionkey FROM nation ORDER BY regionkey FETCH FIRST ROW WITH TIES;
    name    | regionkey 
------------|-----------
 ALGERIA    |         0 
 ETHIOPIA   |         0 
 MOZAMBIQUE |         0 
 MOROCCO    |         0 
(4 rows)
```