v.20.11New Feature

Implement OFFSET and FETCH for SQL-standard LIMIT in SELECT queries

Official CH changelog text
Implement OFFSET offset_row_count {ROW | ROWS} FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} {ONLY | WITH TIES} in SELECT query with ORDER BY. This is the SQL-standard way to specify LIMIT. #15855 (hexiaoting).
Implemented SQL-standard OFFSET offset_row_count {ROW | ROWS} FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} {ONLY | WITH TIES} syntax in SELECT queries with ORDER BY as an alternative to the LIMIT clause.

Why it matters

This feature introduces a standardized and more expressive way to paginate query results following the SQL standard. It improves compatibility with other SQL databases and allows users to specify row offsets and fetch counts clearly, including options to fetch tied rows with WITH TIES.

How to use it

Use the new syntax in SELECT queries with ORDER BY like this:

SELECT * FROM table_name
ORDER BY column_name
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY


You can also use WITH TIES to include extra rows that match the last fetched row's sorting keys.

Related resources

PreviousNext