DataslopeSorting and Limiting
Put SQLite query results in a clear order, then return only the rows you need with LIMIT and OFFSET.
Tables do not have a natural reading order. Even if rows seem to come
back in the same order today, you should not rely on that. If order
matters, say so with ORDER BY.
After the rows are ordered, LIMIT and OFFSET let you take a smaller
piece of the result. Together, they answer questions like "which books
are cheapest?" and "show the next page."
ORDER BY sorts rows
ORDER BY sorts the result by a column. Use ASC for ascending order
(low to high, A to Z) or DESC for descending order (high to low, Z to
A). ASC is the default.
Text can be sorted too. For ordinary text, ascending order is alphabetical.
Sort by more than one column
You can give SQLite a first sort key, then a second sort key for ties. Think of sorting books by genre first, and then by price inside each genre.
The order of columns in ORDER BY sets priority. The first column is
the main sort. Later columns break ties.
LIMIT keeps the first rows of the sorted result
LIMIT n keeps only the first n rows. When you use it with
ORDER BY, you can ask for "top N" or "bottom N" lists.
LIMIT needs order when meaning matters
LIMIT 3 without ORDER BY means "some three rows." If you mean the
cheapest three, newest three, or first three alphabetically, sort first.
OFFSET skips rows
OFFSET n skips the first n rows before returning results. Combined
with LIMIT, it is how websites make pages of results.
This orders all books by price, skips the two cheapest, then returns the next two.
Clause order
When these clauses appear together, write them in this order:
You can leave out WHERE, ORDER BY, LIMIT, or OFFSET when you do
not need them. But if you include them, keep the order shown above.
Check your understanding
Which clause sorts query results into a deliberate order?
WHERE
ORDER BY
LIMIT
SELECT
Why is LIMIT usually paired with ORDER BY?
LIMIT cannot run without ORDER BY.
Rows have no reliable natural order, so sorting makes the chosen rows meaningful.
ORDER BY deletes extra rows.
LIMIT changes ascending order into descending order.
What does LIMIT 2 OFFSET 2 do after results have been sorted?
Returns the first four rows.
Skips the first two rows and returns the next two.
Skips the first four rows.
Returns every row except the first two.
In ORDER BY genre ASC, price DESC, what is the first priority?
price descending
genre ascending
title alphabetical
row insertion order