Sorting and Limiting

Without ORDER BY, a database makes no promise about row order. It returns rows in whatever order was cheapest, which often looks sorted because that is how they happen to be stored. Add an index, change the amount of data, or run the query on a bigger server, and the order can change with no warning and no error.

So the rule is simple to state: if the order matters, say so. Never rely on the order you observed.

-- Ascending is the default
SELECT product_name, unit_price
FROM products
ORDER BY unit_price;

-- Most expensive first
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC;

-- Group by category alphabetically, then price high to low within each
SELECT category, product_name, unit_price
FROM products
ORDER BY category ASC, unit_price DESC;

• ASC means ascending and is the default, so it is usually left out. DESC means descending and must be written.
• With several sort columns, the second is only consulted where the first ties. DESC applies to the one column it follows, not to the whole list.
• ORDER BY is resolved after SELECT, so you can sort by an alias you defined in the SELECT list.

NULL is not greater or smaller than a real value, so each database picks a convention. SQL Server and MySQL sort NULLs first when ascending. PostgreSQL and Oracle sort them last.

If it matters, be explicit. PostgreSQL and Oracle accept ORDER BY credit_limit DESC NULLS LAST. SQL Server does not support that clause, so you sort on a flag first.

SELECT c.company_name, c.credit_limit
FROM customers AS c
ORDER BY
    CASE WHEN c.credit_limit IS NULL THEN 1 ELSE 0 END,
    c.credit_limit DESC;

• The CASE expression produces 0 for rows with a value and 1 for rows without, so sorting on it puts the NULLs at the end.
• The real sort then runs as a tiebreaker within each group. CASE is SQL's if-then-else expression and appears throughout the rest of this course.

-- Top 10, SQL Server
SELECT TOP 10 p.product_name, p.unit_price
FROM products AS p
WHERE p.discontinued = 0
ORDER BY p.unit_price DESC;

-- Page 3 of 20-row pages, standard form
SELECT o.order_id, o.order_date, o.shipping_fee
FROM orders AS o
ORDER BY o.order_date DESC, o.order_id DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

• OFFSET 40 skips 40 rows, so page 3 of 20-row pages starts at row 41. The offset is (page number - 1) multiplied by page size.
• The second sort column, order_id DESC, is a tiebreaker. Without it, two orders on the same date have no defined relative order, so a row can appear on both page 2 and page 3 while another never appears at all.
• That tiebreaker needs to be unique. order_id is the primary key, which makes the whole sort deterministic.