Sorting and limiting results

ORDER BY

SELECT name, total FROM orders
ORDER BY total DESC;

ASC is the default and can be omitted. Sort by several columns by listing them — each direction is set independently:

ORDER BY city ASC, total DESC

That sorts cities alphabetically, and within each city puts the largest orders first.

Sorting by alias or position

ORDER BY runs after SELECT, so unlike WHERE it can use column aliases:

SELECT price * qty AS revenue
FROM sales
ORDER BY revenue DESC;

Ordering by position — ORDER BY 2 — works but breaks the moment someone reorders the SELECT list. Avoid it outside quick exploration.

Where nulls sort

Nulls sort last in ascending order in PostgreSQL and Oracle, first in MySQL and SQL Server. If it matters, be explicit where supported:

ORDER BY total DESC NULLS LAST

Limiting rows

The dialects differ, which is worth knowing for interviews:

SELECT ... ORDER BY total DESC LIMIT 10;              -- Postgres, MySQL, SQLite
SELECT TOP 10 ... ORDER BY total DESC;                -- SQL Server
SELECT ... ORDER BY total DESC FETCH FIRST 10 ROWS ONLY;  -- Oracle, standard SQL

Always pair a limit with ORDER BY. Without one, which ten rows you get is arbitrary and can change between runs.