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?. Top ten customers. Top ten products. Managers love these lists. In SQL, it takes two clauses, order by and limit. Let's build one.
ORDER BY
- ORDER BY. Select the order ID and the order total, from orders. Then order by the order total. That sorts the rows by that column. All hundred and forty one orders, sorted.
SELECT order_id, order_total FROM orders ORDER BY order_total; - Smallest first. But the smallest comes first, just eleven. Order by sorts from low to high, unless you tell it otherwise.
DESC
- DESC. Add DESC, short for descending. Now it sorts from high to low. Run it again.
order_total DESC; - Biggest first. The biggest order is on top now, five hundred and thirteen.
LIMIT
- LIMIT. We only want ten. Add limit ten at the end. It keeps the first ten rows, after the sort. Ten rows. The ten biggest orders.
LIMIT 10;
Top customers
- Top customers. Now top customers by spend. Group by the customer ID, sum each customer's orders as spend, sort by spend descending, and keep ten. Run it. The ten 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 seven is number one, with over twelve hundred and fifty spent. That's who gets the thank you email.
Recap
- The top ten recipe. So, order by sorts, DESC puts the biggest first, and limit keeps the top rows. Always sort before you limit, or the list means nothing.
