03 — Sorting, Pagination & LIMIT

SELECT name, city FROM customers ORDER BY name;               -- ascending (default)
SELECT name, city FROM customers ORDER BY name ASC;           -- explicit ascending
SELECT name, city FROM customers ORDER BY name DESC;          -- descending
SELECT name, city FROM customers ORDER BY city, name;         -- sort by city, then name within city
SELECT name, city FROM customers ORDER BY city DESC, name ASC; -- mixed directions per column

-- ORDER BY 2 means "the second column in the SELECT list"
SELECT name, city FROM customers ORDER BY 2;    -- fragile: reordering SELECT columns silently changes the sort

-- Sort by a computed value — alias is visible in ORDER BY (runs after SELECT)
SELECT name, length(name) AS name_len
FROM customers
ORDER BY length(name) DESC, name;              -- sort by name length desc, then name asc as tiebreaker

-- Sort by a CASE expression — custom ordering not derivable from column values
SELECT name, city FROM customers
ORDER BY
  CASE city
    WHEN 'NYC' THEN 1                           -- NYC first
    WHEN 'LA'  THEN 2                           -- then LA
    ELSE 3                                      -- then everything else
  END,
  name;                                         -- alphabetical within each city tier

-- PostgreSQL: explicit NULLS FIRST / NULLS LAST
SELECT id, ordered_on FROM orders
ORDER BY ordered_on DESC NULLS LAST;           -- NULLs always last, regardless of ASC/DESC

SELECT id, ordered_on FROM orders
ORDER BY ordered_on NULLS FIRST;               -- NULLs always first

-- Portable: ORDER BY (col IS NULL) trick — works on all engines
-- (col IS NULL) returns FALSE (0) for non-NULLs, TRUE (1) for NULLs
-- FALSE sorts before TRUE, so non-NULLs come first, NULLs last — in ascending order
SELECT id, ordered_on FROM orders
ORDER BY (ordered_on IS NULL), ordered_on;     -- NULLs last in ASC, portable across engines

-- For NULLs last in DESC, adjust:
SELECT id, ordered_on FROM orders
ORDER BY (ordered_on IS NULL), ordered_on DESC; -- NULLs still last (boolean sort first), then DESC within non-NULLs

-- First 10 rows (must pair with ORDER BY for deterministic results)
SELECT * FROM orders ORDER BY ordered_on DESC LIMIT 10;

-- Skip 20, take 10 (page 3 of a 10-per-page listing)
SELECT * FROM orders ORDER BY ordered_on DESC LIMIT 10 OFFSET 20;

-- FETCH syntax (ANSI standard, equivalent to LIMIT/OFFSET)
SELECT * FROM orders ORDER BY ordered_on DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Get column names/types without fetching any data — useful for introspection
-- The planner short-circuits: zero rows fetched, only metadata returned
SELECT * FROM orders LIMIT 0;

-- Top 3 amounts, but include any other orders with the same amount as the 3rd row.
-- WITH TIES can return MORE than 3 rows if there are ties at the boundary.
SELECT id, amount FROM orders
ORDER BY amount DESC
FETCH FIRST 3 ROWS WITH TIES;

Page 1: LIMIT 10 OFFSET 0
Page 2: LIMIT 10 OFFSET 10
Page 3: LIMIT 10 OFFSET 20
...
Page N: LIMIT 10 OFFSET (N-1)*10

-- Page 1: fetch the first 10 rows
SELECT id, name, ordered_on
FROM orders
ORDER BY ordered_on DESC, id DESC   -- id as unique tie-breaker (critical!)
LIMIT 10;

-- Suppose the last row was (id=42, ordered_on='2024-03-01').
-- Page 2: fetch rows strictly "before" that row in the sort order.
-- The row-value comparator (ordered_on, id) < (date, id) is lexicographic:
-- compare ordered_on first; if equal, compare id.
SELECT id, name, ordered_on
FROM orders
WHERE (ordered_on, id) < ('2024-03-01', 42)   -- row-value comparison: lexicographic "before"
ORDER BY ordered_on DESC, id DESC
LIMIT 10;

-- Page N: use the last row from page N-1 as the cursor — O(1) seek regardless of depth
SELECT id, name, ordered_on
FROM orders
WHERE (ordered_on, id) < ($last_ordered_on, $last_id)   -- parameterized cursor
ORDER BY ordered_on DESC, id DESC
LIMIT 10;

-- A production-grade keyset pagination query with a composite cursor.
-- Schema: orders(id, customer_id, status, placed_at, total)
-- Index: CREATE INDEX idx_orders_placed_id ON orders (placed_at DESC, id DESC);

-- Page 1 — no cursor, just take the first page
SELECT id, customer_id, status, placed_at, total
FROM orders
ORDER BY placed_at DESC, id DESC
LIMIT 20;

-- Page N — cursor from the previous page's last row
-- $cursor_placed_at and $cursor_id are the last row's values from page N-1
SELECT id, customer_id, status, placed_at, total
FROM orders
WHERE (placed_at, id) < ($cursor_placed_at, $cursor_id)   -- lexicographic "strictly before"
ORDER BY placed_at DESC, id DESC
LIMIT 20;

-- With a WHERE filter on status — the filter applies before the cursor comparison
SELECT id, customer_id, status, placed_at, total
FROM orders
WHERE status = 'shipped'
  AND (placed_at, id) < ($cursor_placed_at, $cursor_id)
ORDER BY placed_at DESC, id DESC
LIMIT 20;
-- Requires a partial index: CREATE INDEX idx_orders_shipped_placed ON orders (placed_at DESC, id DESC) WHERE status = 'shipped';

-- ❌ WRONG: OFFSET pagination on a large table — deep pages are O(N)
SELECT id, name FROM orders ORDER BY placed_at DESC LIMIT 20 OFFSET 1000000;
-- The planner must produce and discard 1,000,000 rows before returning 20.
-- On a 2M-row table, this can take seconds — and gets worse every page.

-- ✅ RIGHT: keyset pagination — O(log N) seek, constant time per page
SELECT id, name FROM orders
WHERE (placed_at, id) < ($cursor_placed_at, $cursor_id)
ORDER BY placed_at DESC, id DESC
LIMIT 20;

-- Page 1
SELECT id, name, ordered_on FROM orders ORDER BY ordered_on DESC LIMIT 10;

-- Page 2
SELECT id, name, ordered_on FROM orders ORDER BY ordered_on DESC LIMIT 10 OFFSET 10;