LIMIT ​

LIMIT returns at most the requested number of rows. If the query also has ORDER BY, it selects rows from that ordered result. An optional offset skips rows first.

Syntax ​

LIMIT count [OFFSET offset]
LIMIT offset, count

The comma form reverses the argument order: LIMIT 1, 2 skips one row and returns up to two rows. LIMIT 2 OFFSET 1 means the same thing.

A negative count removes the SQL row limit. A negative offset acts as zero. Standalone OFFSET and ClickHouse LIMIT BY/WITH TIES syntax are not supported.

LIMIT controls this query's result. It is separate from the server's request-wide max_result_rows setting, which can still reject a result that exceeds its ceiling.

Examples ​

Selecting the highest values ​

CREATE TABLE scores (name TEXT, points INTEGER);
INSERT INTO scores VALUES ('Ada', 100), ('Grace', 100), ('Linus', 80), ('Sam', 60);

SELECT name, points
FROM scores
ORDER BY points DESC, name
LIMIT 2
FORMAT csv;

"Ada",100
"Grace",100

Skipping the first row ​

SELECT generate_series
FROM generate_series(1, 5)
ORDER BY generate_series
LIMIT 2 OFFSET 1
FORMAT csv;

2
3

Use ordering keys that break ties when the exact selected rows matter. See OFFSET for page offsets.