Sorting and taking the top N with ORDER BY and LIMIT

Filtering gets you the right rows; sorting puts them in a useful order and LIMIT keeps just the first few. Together they're the top-N pattern — one of the most common shapes in all of SQL.

The code

A places table with name, country, and population. The three most populous places in Portugal:

SELECT name, country, population
FROM places
WHERE country = 'Portugal'
ORDER BY population DESC
LIMIT 3;

• ORDER BY population DESC — sort biggest first (ASC for smallest first).
• LIMIT 3 — keep only the first 3 rows of the sorted result.

Note the clause order: WHERE filters first, then the survivors get sorted, then the top few are kept. SQL clauses run in a fixed order even though you write them in one breath.

In practice

Leaderboards, latest records, biggest tables, most active users — "give me the top N of X" is everywhere. It's also how you sanity-check a new dataset: ORDER BY some column and LIMIT 10 to eyeball the extremes.

Try it

Practice question: write the query that returns the 3 most populous places in Portugal from scratch — then flip it to return the 3 least populous. Only one keyword changes.

SELECT name, country, population
FROM places
WHERE country = 'Portugal'
ORDER BY population DESC
LIMIT 3;

SELECT name, country, population
FROM places
WHERE country = 'Portugal'
ORDER BY population ASC
LIMIT 3;