Application Query Patterns

Paged Lists

Return One Screen

Application list screens usually fetch a stable slice of rows. ORDER BY, LIMIT, and OFFSET make the page repeatable.

Program

Play the script to choose which two-row page an order list returns.

page_offset
paged_order_list.sql
Replay: real traced execution (multi-file project)
CREATE TABLE page_choice AS WITH params(page_offset) AS (VALUES (0)) SELECT page_offset FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);
INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');
SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);

CREATE TABLE page_choice AS WITH params(page_offset) AS (VALUES (2)) SELECT page_offset FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);
INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');
SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);

CREATE TABLE page_choice AS WITH params(page_offset) AS (VALUES (4)) SELECT page_offset FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);
INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');
SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);

  1. choice ← 1 row

    1CREATE TABLE page_choice AS WITH params(page_offset) AS (VALUES (0)) SELECT page_offset FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);
    values this step1 rowchoice

  2. orders ← 5 rows

    2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);3INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');4SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);
    values this step5 rowsorders

  3. result ← 2 rows

    3INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');4SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE page_choice AS WITH params(page_offset) AS (VALUES (2)) SELECT page_offset FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);
    values this step1 rowchoice

  2. orders ← 5 rows

    2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);3INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');4SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);
    values this step5 rowsorders

  3. result ← 2 rows

    3INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');4SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE page_choice AS WITH params(page_offset) AS (VALUES (4)) SELECT page_offset FROM params;2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);
    values this step1 rowchoice

  2. orders ← 5 rows

    2CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER, status TEXT);3INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');4SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);
    values this step5 rowsorders

  3. result ← 1 row

    3INSERT INTO orders VALUES (101, 'Ada', 40, 'open'), (102, 'Lin', 75, 'paid'), (103, 'Mira', 20, 'open'), (104, 'Noor', 55, 'paid'), (105, 'Ivy', 15, 'open');4SELECT id, customer, total, status FROM orders ORDER BY id LIMIT 2 OFFSET (SELECT page_offset FROM page_choice);
    values this step1 rowresult
stable order `ORDER BY id` keeps the page boundary predictable.
LIMIT `LIMIT 2` returns one screen of rows for this example.
OFFSET The selector changes where the page starts.