ORDER BY, LIMIT & Reliable Pagination — SQL Course

What you'll learn

Produce deterministic result order, control NULL placement, return top rows, and compare offset with keyset pagination. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply ORDER BY to a realistic data question
Apply ASC and DESC to a realistic data question
Apply NULLS FIRST/LAST to a realistic data question
Apply LIMIT and FETCH to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Deterministic order	A total order producing stable row sequence	End ORDER BY with a unique tie-breaker
Offset pagination	Skipping a number of sorted rows	Use for small stable sets, not deep changing feeds
Keyset pagination	Continuing after the last ordered key	Use for scalable, stable forward navigation

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the ordered result contract question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Page with a stable cursor

The tuple comparison follows the same descending order and remains stable when earlier rows are inserted.

book_page.sql

SELECT id, title, published_at
FROM course.books
WHERE (published_at, id) < (TIMESTAMPTZ '2026-06-01 00:00Z', 420)
ORDER BY published_at DESC, id DESC
FETCH FIRST 20 ROWS ONLY;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

LIMIT without ORDER BY asks for an arbitrary subset, and ordering by a non-unique value alone permits unstable ties.

Independent workshop

Build a review-ready ordered result contract lab against the course commerce dataset.

Your finished workshop must include:

ORDER BY
ASC and DESC
NULLS FIRST/LAST
LIMIT and FETCH
OFFSET pagination
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Deterministic order: End ORDER BY with a unique tie-breaker
Offset pagination: Use for small stable sets, not deep changing feeds
Keyset pagination: Use for scalable, stable forward navigation

Quick check

1. Which rule best applies to Deterministic order?

2. Which rule best applies to Offset pagination?

3. Which rule best applies to Keyset pagination?

Next: Phase Project: Explore a Bookstore Database