LIMIT | FETCH FIRST

Updated on 2024-12-13 GMT+08:00

View PDF

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)
```

Parent topic: HetuEngine DQL SQL Syntax

Previous topic: OFFSET

Next topic: TABLESAMPLE

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot