Level 2 · The 10 most recent orders

LIMIT n keeps only the first n rows of the result. Combined with ORDER BY, it answers every “top N” question:

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

The database sorts all rows, then cuts the list after ten. Without ORDER BY, LIMIT 10 gives you ten arbitrary rows, which is fine for peeking at a table and wrong for anything else.

Why the tie-breaker matters even more here

If the 10th and 11th newest orders have the same timestamp, which one makes the cut? Without a tie-breaker, it is up to the database, and the answer can change between runs. With order_id DESC as the second sort key, the result is fully determined. In a report shared with a team, a top-10 list that changes on refresh with no new data is a quick way to lose trust.

Dialects

database	syntax
Postgres, DuckDB, MySQL, BigQuery, Snowflake	LIMIT 10
SQL Server	SELECT TOP 10 ...
SQL standard, Oracle, Postgres, DuckDB	FETCH FIRST 10 ROWS ONLY

LIMIT is what you will see in most analytics job interviews.

Top N with ties

Sometimes the business wants “everyone tied for the top spot”, not exactly 10 rows. Postgres 13+ supports FETCH FIRST 10 ROWS WITH TIES. The portable way is a window function such as RANK(), covered in chapter 8. For now, know that LIMIT always returns exactly n rows (or fewer if the table is smaller), whatever the ties.

LIMIT does not always save work

In DuckDB, BigQuery or Snowflake, SELECT * FROM big_table LIMIT 10 can still scan a lot of data, and in BigQuery you are billed for the full columns scanned regardless of the LIMIT. To explore cheaply there, select fewer columns or use table preview.

Common mistakes

• LIMIT before ORDER BY. It must come last.
• Taking the “top 10 customers” from an unsorted query.
• Using LIMIT to make a slow query fast while developing, then forgetting to remove it before sharing the numbers. If a total looks suspiciously small, look for a leftover LIMIT.