Sorting and Limiting

ORDER BY is a correctness tool — LIMIT without it returns random rows

A top-10 beers report. A "most recent" festival listing. A paginated API. All of them depend on ORDER BY — not just for aesthetics, but for correctness.\n\nPostgreSQL does not guarantee the physical order of rows returned by a SELECT. Without ORDER BY, two identical queries can return rows in different sequences depending on internal page layout, parallelism, and vacuum activity. LIMIT on top of that returns an arbitrary subset, not the "top" anything.

Why ORDER BY Is a Correctness Tool

PostgreSQL stores rows in heap pages and retrieves them in physical storage order by default. That order is not insertion order, not alphabetical order, and not consistent between queries. It changes silently after VACUUM, autovacuum, parallel execution, and even routine updates.

LIMIT 10 without ORDER BY does not return the "first 10" rows — it returns whichever 10 rows the storage engine happened to produce first. Run the same query twice and you may get different rows.

Basic Sorting

SELECT name, abv
FROM beers
ORDER BY abv DESC;       -- highest ABV first

SELECT name, abv
FROM beers
ORDER BY abv ASC;        -- lowest ABV first (default when ASC omitted)

Tie-Breaking with Multiple Columns

SELECT name, style, abv
FROM beers
ORDER BY abv DESC, name ASC;
-- If two beers have the same ABV, sort those ties alphabetically by name

LIMIT and OFFSET

-- Top 5 highest-ABV beers
SELECT name, abv
FROM beers
ORDER BY abv DESC
LIMIT 5;

-- Page 2 of 5 (rows 6–10)
SELECT name, abv
FROM beers
ORDER BY abv DESC
LIMIT 5 OFFSET 5;

Handling NULLs in Sort Order

-- By default, NULLs sort LAST in ASC, FIRST in DESC
SELECT name, ibu
FROM beers
ORDER BY ibu ASC NULLS LAST;   -- NULLs at the bottom
ORDER BY ibu DESC NULLS LAST;  -- NULLs at the bottom even in DESC

Non-deterministic order

Without ORDER BY, PostgreSQL returns rows in the order they happen to be retrieved from storage — which depends on physical page layout, buffer cache, parallel workers, and recent VACUUM activity. This order is not guaranteed to be consistent between two executions of the same query.

NULLS FIRST / NULLS LAST

Controls where NULL values appear in a sorted result. By default in PostgreSQL: ASC sorts NULLs last, DESC sorts NULLs first. NULLS LAST forces NULLs to the bottom regardless of sort direction. This is useful when NULLs represent "not yet rated" or "unknown" — values you want at the end of a report.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user to start sorting exercises.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

🏆 Top 5 Highest-ABV Beers

Return the 5 beers with the highest ABV. Use ORDER BY with DESC and LIMIT together — both are required for a correct "top N" result.

SELECT name, style, abv FROM beers ORDER BY abv DESC LIMIT 5;

📄 Page Through Results

Implement basic pagination: fetch rows 6–10 by ABV. Use OFFSET 5 to skip the first page. Note that OFFSET must always be paired with ORDER BY to guarantee which rows are skipped.

SELECT name, abv FROM beers ORDER BY abv DESC LIMIT 5 OFFSET 5;

🔀 Sort by Two Columns

Sort beers by style ascending, then by ABV descending within each style. Multi-column ORDER BY ensures that ties in the first column are resolved deterministically.

SELECT name, style, abv FROM beers ORDER BY style ASC, abv DESC LIMIT 10;

❓ Handle NULLs in Sort Order

Sort beers by IBU ascending and force NULLs (unknown IBU) to appear at the bottom. Without NULLS LAST, NULLs would appear first in a DESC sort — confusing to end users.

SELECT name, ibu FROM beers ORDER BY ibu ASC NULLS LAST LIMIT 10;

Lab complete! Your results are now deterministic and correctly ranked:\n\n\n ORDER BY col DESC : ✅ highest first\n LIMIT n : ✅ cap at n rows\n OFFSET n : ✅ skip first n rows\n Multi-col sort : ✅ deterministic tie-breaking\n NULLS LAST : ✅ unknowns at the bottom\n

Enable JavaScript to run the live terminal and track your progress.