DataslopeSorting and Limiting
Putting results in a deliberate order with ORDER BY, and grabbing just the top rows with LIMIT — the tools behind every "top 10" list.
Recall that a table is an unordered bag of rows. If you want
your results in a particular order, you must say so. That is what
ORDER BY is for. And once results are ordered, LIMIT lets you
keep just the first few — together they power every "top sellers"
and "most recent" list you have ever seen.
ORDER BY: deliberate order
ORDER BY sorts the result by one or more columns. Add ASC for
ascending (the default) or DESC for descending:
Swap DESC for ASC (or remove it) to flip to lowest-first.
Sorting works on text too — text sorts alphabetically.
Sorting by more than one column
When the first column ties, a second sort column breaks the tie. Think of it like sorting a class first by grade, then by name within each grade:
The rows are grouped by department alphabetically, and within each
department, sorted by salary high to low. The order of the columns
in ORDER BY is the order of priority.
LIMIT: just the top rows
LIMIT n keeps only the first n rows of the result. Combined with
ORDER BY, it answers "top N" questions:
LIMIT without ORDER BY is meaningless
Because rows have no inherent order, LIMIT 3 without an
ORDER BY gives you "some three rows" — and which three is
unpredictable. Always pair LIMIT with ORDER BY when you mean
"the top three." If you genuinely just want a quick peek at any few
rows, that is the one acceptable exception.
Skipping rows with OFFSET
OFFSET n skips the first n rows before returning. OFFSET
together with LIMIT is how applications show "pages" of results —
page 1 is LIMIT 10 OFFSET 0, page 2 is LIMIT 10 OFFSET 10, and
so on:
The query orders everyone, skips the top 2, and returns the next 2 — Carol and Dave. This is the engine behind "Next page" buttons across the web.
Putting the clauses in order
So far you have met four clauses. They must appear in this order:
You can leave out the middle ones, but whichever you include must follow this sequence. Select from a table, filter, sort, then trim — a pipeline that reads almost like a sentence.
Check your understanding
Which clause puts query results into a specific, deliberate order?
WHERE
ORDER BY
LIMIT
SELECT
Why should LIMIT 5 almost always be paired with an ORDER BY?
Because LIMIT will error without ORDER BY.
Because rows have no inherent order, so without ORDER BY you get an unpredictable five rows rather than a meaningful "top five."
Because ORDER BY makes LIMIT run faster.
Because LIMIT only works on sorted tables.
You want the second page of results, showing rows 3 and 4 of an ordered list. Which clause combination does this?
LIMIT 4
LIMIT 2 OFFSET 2
OFFSET 4
WHERE row = 2
In ORDER BY dept ASC, salary DESC, how are rows arranged?
Only by salary, ignoring department.
Randomly, because two columns conflict.
First by department ascending; rows with the same department are then ordered by salary descending.
By salary first, then department.
Practice challenge
Return the title and year of the two most recently published books, newest first.