Sorting and limiting results

ORDER BY sorts the rows you get back. LIMIT keeps only the first few.

Without ORDER BY, the database returns rows in whatever order is fastest for it. That order can change between runs, so never rely on it.

ORDER BY

SELECT opportunity_id, name, amount
FROM opportunities
ORDER BY amount DESC;

• ASC sorts smallest first (A to Z, oldest first). It’s the default, so you can leave it out.
• DESC sorts largest first (Z to A, newest first).

Sorting by more than one column

The second column only breaks ties in the first:

SELECT name, stage, amount
FROM opportunities
ORDER BY stage, amount DESC;   -- stages A to Z, then largest deal first within each stage

Each column gets its own ASC or DESC. In the example above, DESC applies only to amount.

Where NULLs go

Databases disagree on whether NULL sorts first or last. PostgreSQL and SQLite (the sandbox) let you choose:

ORDER BY close_date NULLS LAST

SQL Server doesn’t support NULLS LAST. It always puts NULLs first in ascending order.

LIMIT: the top N rows

SELECT opportunity_id, name, amount
FROM opportunities
ORDER BY amount DESC
LIMIT 10;   -- the 10 largest deals

LIMIT without ORDER BY gives you 10 random-ish rows, not the “top” 10.

Snowflake notes

Snowflake accepts both ... LIMIT 10 at the end and SELECT TOP 10 ... at the start. It supports NULLS FIRST and NULLS LAST, and by default sorts NULLs last in ascending order and first in descending order.

Try it

List every open opportunity worth $50,000 or more, largest first. Return name and amount. Field guide exercise B1 is a longer version of this.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson