Limit

The Limit clause slices the stream by row position using Python-style half-open range syntax. limit n takes the first n rows; limit n.. skips the first n; limit n..m selects the half-open index range [n, m).

Syntax

Railroad

limitintegerinteger..integer..integerinteger..integer..integer

BNF

limit-clause ::= "limit" limit-range

limit-range ::= integer
              | integer ".." [ integer ] [ ".." integer ]

Rules

  1. limit n is shorthand for limit 0..n. (phase: after order by, before select)
  2. Range start..end..step is half-open: indices start, start+step, … strictly less than end are emitted. (phase: before select)
  3. Omitted start defaults to 0; omitted end is unbounded; omitted step is 1. (phase: before select)
  4. start, end, and step must be non-negative integer literals; step must be ≥ 1. (phase: parse / static)

Examples

Minimal

Limit N

Limit N takes the first N rows in scan order.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});

select * from T limit 2;

Result

[
  { "x": 1 },
  { "x": 2 }
]

Limit 0

Limit 0 emits no rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2});

select * from T limit 0;

Result

[]

Limit Greater

Limit greater than the row count returns all rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2});

select * from T limit 10;

Result

[
  { "x": 1 },
  { "x": 2 }
]

Limit N..

Limit N.. skips the first N rows and keeps the rest.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});

select * from T limit 2..;

Result

[
  { "x": 3 },
  { "x": 4 },
  { "x": 5 }
]

Skipping Past

Skipping past the end yields no rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select * from T limit 5..;

Result

[]

Limit 0..

Limit 0.. skips nothing and returns all rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select * from T limit 0..;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 }
]

Limit N..M

Limit N..M is half-open over indices [N, M).

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});

select * from T limit 1..3;

Result

[
  { "x": 2 },
  { "x": 3 }
]

Limit Slice Empty

A slice with M == N emits nothing.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});

select * from T limit 3..3;

Result

[]

Last Row

A slice whose end runs past the data takes through the last row.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4});

select * from T limit 1..10;

Result

[
  { "x": 2 },
  { "x": 3 },
  { "x": 4 }
]

Limit Slices

Limit slices the post-where stream, not the raw scan.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4});

select * from T where T.x > 1 limit 2;

Result

[
  { "x": 2 },
  { "x": 3 }
]

Limit Applies

Limit applies to a scalar projection.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select T.x from T limit 2;

Result

[
  1,
  2
]

See also