Lesson 01 introduced ORDER BY and LIMIT. This lesson goes deeper: stable sorts, dropping duplicates, and the two ways to page through a result set — including why one of them quietly breaks in production.
The seed loaded an articles table — 30 rows, several authors, and a few intentional ties on published_at.
ORDER BY: pick a direction
ORDER BY col sorts ascending (small to large, oldest first). Add DESC to flip it.
SELECT title, views
FROM articles
ORDER BY views DESC
LIMIT 5;
The top-5 most-viewed posts. Without LIMIT 5, you'd get all 30, sorted.
Tie-breakers
What happens when two rows have the same sort key? Postgres returns them in some order — but which one is implementation-defined. If the order matters, spell out a tie-breaker.
SELECT title, author, published_at
FROM articles
ORDER BY published_at, id;
published_at has duplicates (Ada's two articles, Grace's first two — see the seed). Adding id as a second sort key makes the result deterministic: same query, same order, every time. For pagination this isn't optional — it's a correctness requirement, as we'll see in a minute.
NULLS FIRST / NULLS LAST
Postgres puts NULLs last in ascending sorts and first in descending sorts. Override with NULLS FIRST or NULLS LAST when you want the opposite — most often when you want "newest first, but missing dates at the bottom".
SELECT title, published_at
FROM articles
ORDER BY published_at DESC NULLS LAST;
The seed now includes a couple of NULL published_at values so you can see this directly.