SQL ORDER BY and LIMIT: Top 10 Lists
ORDER BY sorts a result, DESC flips it to biggest first, and LIMIT keeps only the top rows. Sort orders by value, then build a top ten list of customers by total spend.
Step by step
Top ten lists
- Who are the top ten?. Managers often ask for top 10 lists, like the top 10 customers or the top 10 products. In SQL you build one with 2 clauses: ORDER BY and LIMIT.
ORDER BY
- ORDER BY. Start with SELECT order_id, order_total FROM orders, then add ORDER BY order_total. This sorts the rows by the order_total column. Run it. All 141 orders come back, sorted by order_total.
SELECT order_id, order_total FROM orders ORDER BY order_total; - Smallest first. The smallest order is on top, at $11. ORDER BY sorts from low to high unless you tell it to do something else.
DESC
- DESC. Change the last line to ORDER BY order_total DESC. DESC is short for descending, so the sort now goes from high to low. Run the query again. It still returns all 141 orders, but in the opposite order.
order_total DESC; - Biggest first. The biggest order is on top now, at $513.
LIMIT
- LIMIT. We only want 10 rows, so add LIMIT 10 on a new line at the end, after ORDER BY. LIMIT keeps the first 10 rows after the sort. Run it and you get 10 rows: the 10 biggest orders.
LIMIT 10;
Top customers
- Top customers. Now find the top customers by spend. GROUP BY customer_id puts each customer's orders together. SUM(order_total) AS spend adds them up. ORDER BY spend DESC puts the biggest spender first, and LIMIT 10 keeps 10 customers. Run it. You get 10 rows: the 10 customers who spent the most.
SELECT customer_id, SUM(order_total) AS spend FROM orders GROUP BY customer_id ORDER BY spend DESC LIMIT 10; - The best customer. Customer 7 is number 1, with over $1,250 spent. That is the customer who should get the thank-you email.
Recap
- The top ten recipe. ORDER BY sorts, DESC puts the biggest first, and LIMIT keeps the top rows. Always sort before you limit. Without ORDER BY, the 10 rows you get have no meaning.
Common mistakes
- Using LIMIT without ORDER BY. Without ORDER BY, the database returns rows in no guaranteed order, so LIMIT 10 just gives you any 10 rows. Add ORDER BY on the column you are ranking by.
- Forgetting DESC. ORDER BY sorts from low to high by default, so ORDER BY order_total LIMIT 10 gives the 10 smallest orders, starting at $11. Add DESC to get the biggest first.
- Putting LIMIT before ORDER BY. LIMIT goes after ORDER BY, at the end of the query (only things like OFFSET or FOR UPDATE can follow it). If you put it before ORDER BY, the query gives a syntax error.
- Ties at the cut-off. If the 10th and 11th customers spent the same amount, LIMIT 10 keeps one of them and drops the other with no clear rule. Add a second sort column, like ORDER BY spend DESC, customer_id, so you get the same result every time.
When a business analyst uses this
Before a workshop on a new loyalty scheme, a business analyst runs the top 10 customers by spend query on the orders table. The list shows stakeholders which customers the change affects most. It also gives the team real customer IDs to use in test cases and acceptance criteria.
Questions people ask
Can you use ORDER BY and LIMIT together in SQL?
Yes. That is the usual way to get a top 10 list. ORDER BY sorts the rows and LIMIT keeps the first ones, for example ORDER BY order_total DESC LIMIT 10. LIMIT works in MySQL, PostgreSQL and SQLite. SQL Server uses TOP instead.
Does LIMIT go before or after ORDER BY?
After. Write ORDER BY first and LIMIT at the end of the query. The database sorts the rows first and then keeps the number you asked for.
How do I get only the single biggest value with ORDER BY DESC LIMIT 1?
Sort high to low and keep 1 row, for example SELECT order_id, order_total FROM orders ORDER BY order_total DESC LIMIT 1. In this lesson's data, that returns the $513 order.
