7.9.11. SQL-11 — `take` and `skip`: Paging

Source shape	Emitted SQL
`take(n)`	`LIMIT ?`
`skip(n)` (alone)	`LIMIT -1 OFFSET ?`
`take(n) \|> skip(m)`	`LIMIT ? OFFSET ?`
`take` followed by `_first()`	`LIMIT 1` (single-row terminal wins)

7.9.11.1. `take(n)` — LIMIT

let first_two <- _sql(db |> select_from(type<Car>) |> take(2))
// SELECT "Id", "Name", "Price" FROM "Cars" LIMIT ?

7.9.11.2. `skip(n)` — OFFSET

SQLite requires a LIMIT whenever OFFSET is present, so a bare skip(n) emits LIMIT -1 OFFSET ? (LIMIT -1 is SQLite’s “no limit”):

let after_three <- _sql(db |> select_from(type<Car>) |> skip(3))
// SELECT "Id", "Name", "Price" FROM "Cars" LIMIT -1 OFFSET ?

7.9.11.3. Combined — a paging window

Combine take and skip for a window. Order in the chain doesn’t change the SQL — the macro accumulates both into a single LIMIT ... OFFSET ... clause. Note that take(n) skip(m) is LIMIT n OFFSET m — rows m..m+n-1, NOT “page (m+1) of size n” — standard paging is page k of size n = take(n) skip((k-1) * n):

let window <- _sql(db |> select_from(type<Car>) |> take(2) |> skip(1))
// ... LIMIT ? OFFSET ?     (rows 2..3)

7.9.11.4. Bind ordering

LIMIT and OFFSET binds always go after WHERE binds in the placeholder index sequence. A _where(...) |> take(n) chain produces WHERE col > ? LIMIT ? with binds in [wherevalue, n] order:

let threshold = 100
let cheap_two <- _sql(db |> select_from(type<Car>)
                        |> _where(_.Price > threshold)
                        |> take(2))
// SELECT ... WHERE "Price" > ? LIMIT ?     binds: [100, 2]

7.9.11.5. Single-row terminals override `take`

_first and _first_opt always emit LIMIT 1, ignoring any earlier take. The terminal’s intent (one row) overrides the chain’s pagination hint:

let head = _sql(db |> select_from(type<Car>) |> take(5) |> _first())
// ... LIMIT 1

7.9.11.6. Stable pagination needs `_order_by`

LIMIT/OFFSET without ORDER BY isn’t deterministic — SQLite can return rows in any order. Pair take/skip with SQL-10 — _order_by and _order_by_descending whenever the page boundaries matter:

let stable_page <- _sql(db |> select_from(type<Car>)
                          |> _order_by(_.Price)
                          |> take(2) |> skip(1))
// ... ORDER BY "Price" ASC LIMIT ? OFFSET ?

See also

Full source: tutorials/sql/11-take_skip.das

Previous tutorial: SQL-10 — _order_by and _order_by_descending

Next tutorial: SQL-12 — distinct