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.

paged_order_list.sql 
CREATE TABLE page_choice AS WITH params(page_offset) AS (VALUES ()) 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 ()) 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 ()) 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);
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.