Sorting & Limiting Results

A query rarely just needs "the matching rows" — it usually needs them in a specific order, and often only a handful at a time. ORDER BY controls the sort order of a result set, and LIMIT/OFFSET cut it down to a page at a time. Both are applied after WHERE has already filtered the rows.

ORDER BY — Ascending and Descending

ORDER BY column sorts the results by that column, ascending (smallest/earliest first) by default. Add ASC to be explicit, or DESC to reverse it to descending (largest/latest first).

-- Quickest recipes first
SELECT title, prep_minutes FROM recipes
ORDER BY prep_minutes ASC;

-- Newest recipes first
SELECT title, created_at FROM recipes
ORDER BY created_at DESC;

Ordering by Multiple Columns

List several columns, separated by commas. SQLite sorts by the first column, and only uses the next column to break ties where the first column's value is equal — each column can have its own ASC/DESC direction.

-- Group by difficulty, and within each difficulty, quickest first
SELECT title, difficulty, prep_minutes FROM recipes
ORDER BY difficulty ASC, prep_minutes ASC;

LIMIT — Capping the Result Set

LIMIT n caps the results at, at most, n rows. It's almost always paired with ORDER BY — without an order, "the first 5 rows" isn't a meaningful, stable concept, since SQLite doesn't guarantee any particular row order on its own.

-- The 5 highest-rated recipes
SELECT r.title, AVG(rv.rating) AS avg_rating
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
GROUP BY r.id
ORDER BY avg_rating DESC
LIMIT 5;

OFFSET — Paging Through Results

OFFSET n skips the first n matching rows before LIMIT starts counting. Combined, LIMIT and OFFSET implement pagination: page size stays fixed in LIMIT, and OFFSET moves forward by one page's worth of rows each time.

-- Page 1 of top-rated recipes (rows 1-10)
SELECT r.title, AVG(rv.rating) AS avg_rating
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
GROUP BY r.id
ORDER BY avg_rating DESC
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT r.title, AVG(rv.rating) AS avg_rating
FROM recipes r
JOIN reviews rv ON rv.recipe_id = r.id
GROUP BY r.id
ORDER BY avg_rating DESC
LIMIT 10 OFFSET 10;

Putting It Together

The five quickest easy recipes, alphabetically for ties, as a single page-1 query:

SELECT title, prep_minutes
FROM recipes
WHERE difficulty = 'easy'
ORDER BY prep_minutes ASC, title ASC
LIMIT 5 OFFSET 0;

WHERE narrows the candidates down to easy recipes, ORDER BY sorts them fastest-first (breaking ties by title), and LIMIT/OFFSET hand back just the first page of five.