Chapter 1 · 6 min
Sorting and limiting results
ORDER BY and LIMIT look like presentation details, but interview questions use them to test
whether you understand what “the first N rows” actually means. Without a sort, a LIMIT returns an
arbitrary subset: the engine is free to produce rows in whatever order is cheapest.
SELECT order_id, order_date, status
FROM orders
WHERE status = 'paid'
ORDER BY order_date DESC, order_id DESC
LIMIT 5;
The second sort key is not decoration. If several orders share the same date, the tie-breaker makes the result deterministic, so the same query returns the same five rows on every run. A single non-unique sort key is the most common reason a “top five” answer is quietly unstable.
Sort direction and null placement
ASC is the default and DESC reverses it. Postgres also accepts NULLS FIRST and NULLS LAST,
which decide where missing values appear. The default puts nulls last for ascending order and first
for descending order, which is rarely what a question about “highest values” intends. Write the
placement explicitly when the column can be null.
Sorting by an expression is allowed, including an aggregate or a CASE result:
SELECT customer_id, count(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY paid_orders DESC, customer_id
LIMIT 10;
LIMIT is not a filter
LIMIT caps how many rows leave the query. It does not change which rows qualify, and it does not
guarantee that the rows you get are the ones you meant. “The ten most recent orders” needs a sort
and then a limit; “ten orders” needs only the limit.
OFFSET skips rows before the cap. Paging with LIMIT and OFFSET is fine for a static result,
but it can repeat or skip rows when the underlying data changes between pages. For a stable page
walk, sort by a unique key and remember the last value rather than counting offsets.
The trap to name
If a question asks for “the latest order per customer”, a global ORDER BY order_date DESC LIMIT 1
answers a different question: it returns one row for the whole table. Grouping by customer and
taking a per-group extreme needs either a window function or a join back to a grouped subquery.
Recognising that difference before writing SQL is most of the work.