SQL ORDER BY: DESC, LIMIT and Top 10 Rows
· 6 min read
SQL ORDER BY sorts the rows of your result. It sorts from smallest to largest (ASC) unless you add DESC. Combine it with LIMIT and you get the classic "top N" query: sort the best rows to the top, then keep only the first few.
SELECT product_name, units_sold
FROM products
ORDER BY units_sold DESC
LIMIT 3;That returns the three best-selling products. Without ORDER BY, a database is free to return rows in any order it likes, and LIMIT would just grab whichever rows came first. Below: the output, sorting by several columns, TOP in SQL Server, what to do about ties and NULLs, and the direction mistake that turns a top 3 into a bottom 3.
A worked example
| product_name | category | units_sold | unit_cost |
|---|---|---|---|
| Paper A4 | Office | 1250 | 3.10 |
| Stapler | Office | 140 | 6.80 |
| Gel pens | Office | 860 | 4.20 |
| Coffee beans | Kitchen | 610 | 11.50 |
| Paper cups | Kitchen | 860 | 2.40 |
| Dish soap | Cleaning | 430 | 1.90 |
| Mop head | Cleaning | 95 | NULL |
| Microfibre cloths | Cleaning | 520 | 1.20 |
Run the query from the top on it:
SELECT product_name, units_sold
FROM products
ORDER BY units_sold DESC
LIMIT 3;| product_name | units_sold |
|---|---|
| Paper A4 | 1250 |
| Gel pens | 860 |
| Paper cups | 860 |
ASC vs SQL ORDER BY DESC
ASC (ascending) is the default, so ORDER BY units_sold and ORDER BY units_sold ASC are the same: smallest first. Numbers go low to high, dates go old to new, and text goes A to Z.
DESC (descending) flips it: largest first, newest date first, Z to A. Use it for anything described as "top", "highest", "most", "latest" or "biggest".
The keyword applies to one column only. In ORDER BY category, units_sold DESC, the category is ascending and the units are descending.
Sorting by multiple columns
List columns separated by commas. SQL sorts by the first one, then uses the second to order rows that tie on the first, and so on:
SELECT category, product_name, units_sold
FROM products
ORDER BY category, units_sold DESC;| category | product_name | units_sold |
|---|---|---|
| Cleaning | Microfibre cloths | 520 |
| Cleaning | Dish soap | 430 |
| Cleaning | Mop head | 95 |
| Kitchen | Paper cups | 860 |
| Kitchen | Coffee beans | 610 |
| Office | Paper A4 | 1250 |
| Office | Gel pens | 860 |
| Office | Stapler | 140 |
Categories run A to Z, and inside each one the best sellers come first.
You can also sort by an alias from SELECT, because ORDER BY runs after SELECT. ORDER BY cost_of_sales DESC works if cost_of_sales is defined in the SELECT list. Sorting by position, ORDER BY 2 DESC, works too, but it breaks silently when someone reorders the columns, so use names.
SQL LIMIT and SQL TOP 10
Different databases spell "first N rows" differently:
| Database | Top 10 syntax |
|---|---|
| DuckDB, Postgres, MySQL, SQLite | ... ORDER BY x DESC LIMIT 10 |
| SQL Server | SELECT TOP 10 ... ORDER BY x DESC |
| Standard SQL (DuckDB, Postgres, Oracle 12c+) | ... ORDER BY x DESC FETCH FIRST 10 ROWS ONLY |
| SQL Server, standard style | ... ORDER BY x DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY |
Note that TOP goes right after SELECT, while LIMIT goes at the very end. They are not interchangeable: SELECT TOP 3 ... is a syntax error in DuckDB and Postgres, and SQL Server does not accept LIMIT. If an AI assistant hands you SQL Server code for a Postgres database, this is the first thing to change.
Paging with OFFSET
OFFSET skips rows before LIMIT starts counting. To get products 4 to 6:
SELECT product_name, units_sold
FROM products
ORDER BY units_sold DESC, product_name
LIMIT 3 OFFSET 3;| product_name | units_sold |
|---|---|
| Coffee beans | 610 |
| Microfibre cloths | 520 |
| Dish soap | 430 |
The extra product_name in the sort is important, and the next section explains why.
Ties: make the order unique
Gel pens and Paper cups both sold 860. In ORDER BY units_sold DESC, SQL has no rule for which comes first, and the order can change between runs or databases. With LIMIT 2 you would get Paper A4 plus one of the two, picked arbitrarily. That is a real problem in a report: the same query shows different products on different days.
Two fixes:
- Add a tiebreaker column so the order is fully defined:
ORDER BY units_sold DESC, product_name. - If tied rows should all be included, rank them instead of limiting.
RANK()gives both 860s the same rank. See RANK OVER PARTITION BY for how.
Where do NULLs go?
Mop head has no cost recorded. Databases disagree on where NULLs sort:
| Database | ASC | DESC |
|---|---|---|
| DuckDB | last | last |
| Postgres, Oracle | last | first |
| MySQL, SQL Server, SQLite | first | last |
So in Postgres, ORDER BY unit_cost DESC LIMIT 3 returns Mop head at the top with a blank cost. To control it, say it explicitly:
SELECT product_name, unit_cost
FROM products
ORDER BY unit_cost DESC NULLS LAST
LIMIT 3;| product_name | unit_cost |
|---|---|
| Coffee beans | 11.50 |
| Stapler | 6.80 |
| Gel pens | 4.20 |
NULLS FIRST and NULLS LAST work in DuckDB, Postgres, Oracle and SQLite. In MySQL and SQL Server, filter them out with WHERE unit_cost IS NOT NULL or sort on a CASE expression first.
Common mistakes
Forgetting DESC on a "top" query (the AI mistake to watch for)
Ask an AI assistant for "the 3 best-selling products" and sometimes the DESC is simply missing:
SELECT product_name, units_sold
FROM products
ORDER BY units_sold
LIMIT 3;| product_name | units_sold |
|---|---|
| Mop head | 95 |
| Stapler | 140 |
| Dish soap | 430 |
These are the three worst sellers. The query runs, the result has three rows and the right columns, and the assistant will often describe it as the top 3 anyway. Nothing in the output warns you.
How to spot it: look at the first row. In a "top" list it should hold the biggest value in the table. A quick SELECT MAX(units_sold) FROM products (1250) tells you what row one should show.
Using LIMIT to get the top row per group
"The best seller in each category" is not ORDER BY category, units_sold DESC LIMIT 1. That returns one row in total (Microfibre cloths), not one per category. LIMIT applies to the whole result. Top N per group needs a window function such as ROW_NUMBER() or RANK() with PARTITION BY category.
Sorting inside a subquery or CTE
An ORDER BY inside a subquery does not guarantee the order of the outer result. Put the final ORDER BY in the outermost query.
FAQ
What is the default sort order in SQL ORDER BY?
Ascending (ASC): smallest numbers, earliest dates and A-to-Z text first. Add DESC for the reverse.
How do I select the top 10 rows in SQL?
Sort descending and limit: ORDER BY column DESC LIMIT 10 in DuckDB, Postgres and MySQL, or SELECT TOP 10 ... ORDER BY column DESC in SQL Server.
Can I ORDER BY a column that is not in the SELECT?
Yes, in most cases. SELECT product_name FROM products ORDER BY units_sold DESC is valid. The exception is SELECT DISTINCT, where Postgres requires the sort column to be in the select list.
Does ORDER BY come before or after GROUP BY?
After. The written order is WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Grouped results are covered in GROUP BY multiple columns.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “The AI's most expensive products” →