Add support for LIMIT and OFFSET on AQL syntax

Description

Currently AQL only supports TOP, which is based on the keyword supported by SQLServer to limit the number of results, but current AQL doesn't support an offset to be able to paginate results.

Checking keywords supported by different DB vendors, and also by different ANSI SQL specs, ANSI only added support for this on the latest versions, but DB vendors have their own pagination keywords.

ANSI SQL:2011- OFFSET num {ROW|ROWS} FETCH {FIRST|NEXT} [num] {ROW|ROWS} ONLY

DB2 – select * from table fetch first 10 rows only
Informix – select first 10 * from table
Microsoft SQL Server and Access – select top 10 * from table
MySQL and PostgreSQL – select * from table limit 10
Oracle 8i – select * from (select * from table) where rownum <= 10
Later versions of SQLServer support OFFSET 234 ROWS FETCH NEXT 10 ROWS ONLY

IMO the most readable is LIMIT/OFFSET:

SELECT ...
FROM ...
WHERE ...
LIMIT 10 OFFSET 0

Environment

None

Activity

Show:
Matija Polajnar
September 9, 2019, 6:32 AM

I agree with LIMIT and OFFSET being the best options, however, current AQL spec defines TOP and we should probably also include that for backwards compatibility – although I don’t like to have two ways to express the same simple thing…

Ian McNicoll
September 26, 2019, 8:04 PM

I agree with Matija - support both LIMIT / OFFSET and TOP.

Reporter

Pablo Pazos

Labels

None

Components

Priority

Major
Configure