Sorting and Limiting Rows

The 24-chapter SQL for Beginners series can now select and filter rows; chapter 5 puts them in order and cuts them down to size. ORDER BY controls sequence, LIMIT controls how many rows come back, and the combination is what turns “some rows” into “the top five”, “page two” or “the newest record”.

ORDER BY basics

ORDER BY names one or more expressions to sort by. The default direction is ascending (ASC, lowest or earliest first), and DESC reverses it:

SELECT name, price FROM products ORDER BY price DESC;

Expected output:

Clean Code|899
Python Crash Course|749
Robotics for Kids|649
The Silent Patient|499
Matilda|349
The Very Hungry Caterpillar|249

Drop the DESC and the same query lists the cheapest first. Sorting happens on the result, not on the table: the rows inside products are untouched, and every future query gets to choose its own order. Because WHERE runs before ORDER BY, a filter and a sort combine freely — put the WHERE clause first and the ORDER BY clause last, as always.

Text sorts alphabetically by default, so reverse-alphabetical is one word away:

SELECT first_name, last_name FROM customers ORDER BY last_name DESC;

Expected output:

Marc|Tremblay
Aarav|Sharma
Amara|Ochieng
Priya|Nair
Wei|Ling
Lena|Fischer

Tremblay before Sharma before Ochieng — the sort ran on last_name while the output still shows both columns, which is legal because sorting happens after the select list is built. Notice also that the tiebreak matters: no two surnames collide here, but as soon as they do, rows with equal keys may swap places between runs.

Several keys and expressions

Give ORDER BY a comma-separated list and the sort is lexicographic: the first key dominates, ties are broken by the second, and so on. Cheapest first within each category:

SELECT name, category, price
FROM products
ORDER BY category, price DESC;

Expected output:

The Silent Patient|Fiction|499
Robotics for Kids|Kids|649
Matilda|Kids|349
The Very Hungry Caterpillar|Kids|249
Clean Code|Tech|899
Python Crash Course|Tech|749

Read it as “group the categories alphabetically, then within each group order by price descending”. The keys do not have to be columns you selected — they can be expressions, and they can be output aliases defined in the same SELECT, because sorting runs after the select list exists:

SELECT name, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC;

Expected output:

The Silent Patient|19960
Python Crash Course|16478
Clean Code|16182
The Very Hungry Caterpillar|14940
Matilda|10470
Robotics for Kids|9735

stock_value is computed during SELECT and sorted during ORDER BY — a snapshot of inventory value per product, stored nowhere. Sorting by the output column number (ORDER BY 2) works too and means “the second selected column”, never the table’s second column.

Text, case and NULLs in sort order

Sorting text follows a collation — the rule that says which character comes first. SQLite uses a binary comparison by default, so ordering is byte-wise and uppercase letters sort before lowercase ones (Ben before apple). A few habits keep results predictable:

• Sort dates as ISO strings — 2026-01-12 sorts chronologically because its layout puts the largest unit first. A format such as 12/01/2026 sorts as text and lies to you.
• Do not rely on SQL’s default direction for “newest first” — descending is always DESC, spelled out, on every engine.
• Know where NULLs land — SQLite sorts NULLs first ascending, MySQL also sorts them first, and PostgreSQL sorts them last ascending (reverse that for DESC) unless you write NULLS FIRST or NULLS LAST.
• Remember WHERE runs first — rows removed by a filter never take part in the sort at all.
• Sort on the underlying value — ordering by last_name while selecting a concatenated full_name is normal, because ORDER BY can see every expression the earlier steps produced.

Sorting a set of distinct values works the same way, since ORDER BY runs last in the evaluation timeline from chapter 3:

SELECT DISTINCT city FROM customers ORDER BY city;

Expected output:

Berlin
Delhi
Mumbai
Nairobi
Singapore
Toronto

LIMIT and OFFSET

LIMIT n keeps only the first n rows of the sorted result, and OFFSET m discards m rows before counting begins. The three latest orders:

SELECT id, order_date FROM orders ORDER BY order_date DESC LIMIT 3;

Expected output:

6|2026-03-02
5|2026-02-25
4|2026-02-11

The offset form skips into the result, which is how paging works: LIMIT 2 OFFSET 2 reads rows three and four of the same ordering.

SELECT first_name, city FROM customers ORDER BY country LIMIT 2 OFFSET 2;

Expected output:

Aarav|Mumbai
Priya|Delhi

Sorted by country the order is Canada, Germany, India, India, Kenya, Singapore; skipping Canada and Germany leaves the two Indian customers. Note the syntax: LIMIT takes a count and OFFSET takes a skip, in that order. SQLite and MySQL accept both and also allow the shorthand LIMIT offset, count (easily confused, so prefer the explicit form); PostgreSQL accepts LIMIT/OFFSET and the standard FETCH FIRST 3 ROWS ONLY; SQL Server has no LIMIT at all and uses SELECT TOP (3) instead.

One asymmetry worth memorising: PostgreSQL will run an OFFSET on its own, while SQLite and MySQL want a LIMIT clause in front of it — SQLite’s idiom for “all rows from here on” is the slightly odd-looking LIMIT -1 OFFSET 2. Always pair the two, on every engine, and nobody has to remember which is which.

WHERE, ORDER BY and LIMIT together

The three clauses stack in a fixed order: WHERE chooses the rows, ORDER BY arranges what survived, LIMIT truncates the arrangement. Products priced at or above 450, most expensive first, only three of them:

SELECT name, price
FROM products
WHERE price >= 450
ORDER BY price DESC
LIMIT 3;

Expected output:

Clean Code|899
Python Crash Course|749
Robotics for Kids|649

Four products met the filter, but only the top three survive: the 499-rupee title sorted last of the four and then fell off the end. Read the query top to bottom and it says exactly that — filter, sort, cut — and reordering the clauses is never a style choice: LIMIT before WHERE is a syntax error on most engines, while swapping the other two changes the meaning.

This one shape answers an enormous share of real questions: the latest three orders, the five cheapest products, the top spenders on a leaderboard. Get comfortable writing it as a five-line stack with one clause per line, because chapter 6 will drop a GROUP BY into the middle of it and everything else stays put.

ORDER BY before LIMIT

The two clauses are only meaningful together. Without ORDER BY, LIMIT asks for “any three rows”: the engine may return the same three today and different ones tomorrow, because nothing pins the order down. Whenever you see a bare LIMIT in someone else’s code, the question to ask is “ordered by what?” The same applies to paging: page two of a list is only page two if the ordering is stable.

1. Sort first — choose an ORDER BY with a unique tiebreaker, such as ORDER BY order_date DESC, id DESC, so equal dates cannot swap places between pages.
2. Then skip — OFFSET counts rows in that finished ordering, never table rows.
3. Then take — LIMIT sets the page size, and the same three clauses reappear verbatim on every page.

Here is the pattern running for real — page two of the orders list, two rows at a time, with the tiebreaker in place:

SELECT id, order_date
FROM orders
ORDER BY order_date DESC, id DESC
LIMIT 2 OFFSET 2;

Expected output:

4|2026-02-11
3|2026-02-03

Rows one and two of the descending order are orders 6 and 5, so skipping two lands on 4 and 3 — exactly page two of the sequence the same query returns with LIMIT 2 OFFSET 0. Change nothing but the offset and the pages march on in order.

Keep one caveat in view for later: large offsets make the engine walk past every skipped row, so deep pages get progressively slower. Chapter 19 covers indexes, and chapter 23 covers how an application should page; for now, LIMIT with a deterministic ORDER BY is the correct pattern.

Common mistakes

• LIMIT without ORDER BY — you get an arbitrary handful of rows, and the next run may well hand you a different handful.
• Forgetting the default is ascending — “give me the newest orders” needs DESC; without it you get the oldest.
• Reading ORDER BY 2 as a table column — the number refers to a position in the select list, so it tracks the columns you selected, never the table’s own layout.
• Swapping LIMIT and OFFSET — LIMIT 2 OFFSET 3 starts at row four and stops after two; the arguments are not interchangeable.
• Sorting dates in a local format — 12/01/2026 sorts alphabetically and gives an order that is not chronological; store and sort ISO dates.
• Assuming the sort is stable — rows that share a key may swap between runs, so add a unique second key such as id whenever the output must be identical every time.

Practice checklist

1. List the five most expensive products, and verify the output against the ORDER BY price DESC result from this chapter.
2. Sort customers by signup_date ascending, then rewrite the query to show the newest signup first.
3. Page through orders with LIMIT 3 OFFSET 0, OFFSET 3 and OFFSET 6, and note which pages are full.
4. Sort products by category ASC, stock DESC and predict the six-line result before running it.
5. Run a query with LIMIT 3 and no ORDER BY three times, and look for any row movement between runs.

What's next?

Individual rows can now be found and arranged; next the series stops looking at rows one by one and starts summarising them: Aggregate Functions, GROUP BY and HAVING.