SQL Sorting & Pagination Practice

LIMIT requires ORDER BY to include a unique column.

PostgreSQL docs explicitly state: "If the ORDER BY does not uniquely order the result set, you may receive an unpredictable subset of rows."

-- BAD: Ties in created_at cause unpredictable results
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

-- GOOD: id breaks ties deterministically
SELECT * FROM users
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

Why this matters: If two users have the same created_at, their order is undefined. When new data is inserted or deleted, rows shift between pages. Users see duplicates on page 3 that were already on page 2, or skip rows entirely.

OFFSET pagination (simple but slow)

-- Page 1
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- Page 2
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 20;

-- Page 50
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 980;

Problem: OFFSET 980 means the database finds 1,000 rows, sorts them, discards 980, and returns 20. The deeper the page, the slower it gets—OFFSET cost scales linearly with the page number. This is why the APIs for Slack, Stripe, and GitHub all use cursor-based pagination instead of OFFSET for their public endpoints.

Keyset pagination (fast for any depth)

-- Page 1
SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: use last row's values as cursor
SELECT * FROM posts
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Why it's fast: The database uses an index to seek directly to the cursor position. No rows are scanned and discarded. Page 50 is as fast as page 1. The PostgreSQL wiki's pagination guide explicitly recommends keyset pagination over OFFSET for large datasets.

Trade-off: You can't jump to arbitrary pages. You can only go "next" or "previous" from a known position. This is fine for feeds, infinite scroll, and "Load More" buttons.

Keyset pagination for ASC order

-- Ascending order: use > instead of <
SELECT * FROM posts
WHERE (created_at, id) > (:cursor_created_at, :cursor_id)
ORDER BY created_at ASC, id ASC
LIMIT 20;

MySQL keyset (expanded form)

MySQL 5.7+ supports tuple comparison, but older versions require manual expansion. The expanded form also works if the optimizer struggles with tuple comparisons:

-- Equivalent to WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
SELECT * FROM posts
WHERE created_at < :cursor_created_at
   OR (created_at = :cursor_created_at AND id < :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

By default, NULL sorts high in ASC order and low in DESC order—but this varies by database.

NULLS FIRST/LAST syntax (PostgreSQL, SQLite 3.30+)

-- Put unshipped orders last
SELECT * FROM orders
ORDER BY shipped_at ASC NULLS LAST;

-- Put unshipped orders first
SELECT * FROM orders
ORDER BY shipped_at DESC NULLS FIRST;

Portable workaround (works everywhere)

-- NULLs last: (column IS NULL) returns 0 for non-NULL, 1 for NULL
SELECT * FROM orders
ORDER BY (shipped_at IS NULL) ASC, shipped_at ASC;

-- NULLs first
SELECT * FROM orders
ORDER BY (shipped_at IS NULL) DESC, shipped_at ASC;

How it works: (shipped_at IS NULL) returns FALSE (0) for non-NULL values and TRUE (1) for NULL. Sorting by this expression first groups NULLs together, then the second column orders within each group.

If your ORDER BY matches an index, the database can read rows in sorted order without a separate sort step.

-- Create an index that matches your ORDER BY
CREATE INDEX idx_users_created_at_id
ON users (created_at DESC, id DESC);

-- This query can now use the index for ordering
SELECT * FROM users
ORDER BY created_at DESC, id DESC
LIMIT 20;

Index direction matters

PostgreSQL allows specifying ASC/DESC and NULLS FIRST/LAST per column in indexes:

-- Match your query's exact ordering
CREATE INDEX idx_orders_status
ON orders (shipped_at ASC NULLS LAST, id ASC);

MySQL 8.0+, SQLite, and PostgreSQL all support specifying ASC/DESC in indexes. Older MySQL versions (5.7 and earlier) ignored index direction and always scanned forward or backward as needed.

When indexes don't help

• Small result sets: Sorting a few hundred rows is cheap; the optimizer may skip the index.
• Functions on columns: ORDER BY LOWER(name) can't use an index on name. Create an expression index: CREATE INDEX ON users (LOWER(name)).
• Missing WHERE clause: If you're ordering the entire table, the index scan may not help.

ORDER BY is also critical inside window function OVER clauses and for deterministic results when aggregating sorted data.

Map values to sort priorities:

-- Priority-based ordering
SELECT * FROM tickets
ORDER BY
  CASE status
    WHEN 'urgent' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    WHEN 'low' THEN 4
    ELSE 5
  END,
  created_at DESC,
  id DESC;

Handling special values

-- "Featured" items first, then by date
SELECT * FROM products
ORDER BY
  CASE WHEN is_featured THEN 0 ELSE 1 END,
  created_at DESC,
  id DESC;

-- NULL priorities treated as lowest
SELECT * FROM tasks
ORDER BY COALESCE(priority, 999) ASC, id ASC;

The SQL standard uses OFFSET ... FETCH instead of LIMIT:

-- SQL standard syntax
SELECT * FROM products
ORDER BY price DESC, id DESC
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;

-- Equivalent to
SELECT * FROM products
ORDER BY price DESC, id DESC
LIMIT 10 OFFSET 20;

For maximum portability across MySQL and SQL Server, you'd need to use different syntax for each.

1. Duplicate rows across pages? Missing unique tie-breaker in ORDER BY. Add id or another unique column.

2. Skipped rows? Same cause—ties plus data changes between page loads. Use keyset pagination or add tie-breaker.

3. Random order on refresh? Missing ORDER BY entirely, or ORDER BY on a column with all identical values.

4. Deep pages are slow? OFFSET scans and discards. Switch to keyset pagination or add an appropriate index.

5. NULLs in wrong position? Default NULL ordering varies by database. Use NULLS FIRST/LAST or the portable workaround.

6. ORDER BY not using index? Check that index column order matches ORDER BY, including direction.

• PostgreSQL: LIMIT and OFFSET
• PostgreSQL: Index Ordering (ASC/DESC, NULLS FIRST/LAST)
• MySQL: LIMIT Clause
• MySQL: ORDER BY Optimization
• Use The Index, Luke: Pagination
• SQLite: NULL Handling in ORDER BY