Lesson 5 - Sorting and Limiting Results

Welcome to Sorting and Limiting Results#

Until now your results have come back in whatever order the database happened to store them. In this lesson you take control of that order with ORDER BY, and you combine it with LIMIT to answer some of the most common questions in analytics: “What are the top 10 most profitable orders?” or “Which products sold the least?”

You will also learn the formatting conventions that make SQL readable — the small habits that separate professional queries from messy ones.

By the end of this lesson, you will be able to:

• Sort results in ascending and descending order with ORDER BY
• Sort by multiple columns, each with its own direction
• Understand ordinal sorting and why named columns are preferred
• Combine ORDER BY with WHERE and LIMIT to build Top-N and Bottom-N lists
• Place ORDER BY correctly in SQL’s order of execution
• Format queries cleanly using professional style conventions

You should be comfortable with SELECT, WHERE, LIMIT, and aliases. Let’s begin.

Sorting Ascending and Descending#

ORDER BY sorts your results. It belongs after WHERE and before LIMIT:

Ascending Order (the Default)#

By default, ORDER BY sorts ascending: A-Z for text, low-to-high for numbers. To see Superstore’s smallest sales:

SELECT product_name, quantity, sales
  FROM orders
 ORDER BY sales;

The cheapest sale floats to the top.

Descending Order#

Add DESC after a column to reverse the sort — Z-A for text, high-to-low for numbers. To see the most expensive sales:

SELECT product_name, quantity, sales
  FROM orders
 ORDER BY sales DESC;

Now the priciest sale comes first. To find the most profitable orders, you would sort ORDER BY profit DESC.

Sorting by Multiple Columns#

You can sort by several columns at once. SQL sorts by the first column, then uses the next column to break ties, and so on.

Tie-Breaking with a Second Column#

To list each customer’s orders alphabetically by name, then by quantity:

SELECT customer_name, quantity
  FROM orders
 ORDER BY customer_name, quantity;

Names sort A-Z; within each name, quantities sort low-to-high. Each column can have its own direction. To keep names ascending but show the largest quantities first, add DESC to just that column:

SELECT customer_name, quantity
  FROM orders
 ORDER BY customer_name, quantity DESC;

The DESC applies only to quantity; customer_name stays ascending. You can mix directions freely — for example, ORDER BY discount DESC, profit sorts by largest discount first, then by lowest profit within each discount.

Ordering by Ordinal Position#

You may sometimes see a sort written with numbers instead of column names:

SELECT order_id, customer_name, state, product_name
  FROM orders
 ORDER BY 2, 3 DESC;

The 2 and 3 are ordinal positions — they refer to the 2nd and 3rd columns in the SELECT list. It works, but you should avoid it. It is hard to read (you have to count columns), and it breaks silently if you reorder the SELECT list later. Always sort by column name so your intent is clear and your query is safe to edit.

Combining ORDER BY with WHERE and LIMIT#

ORDER BY becomes far more useful alongside WHERE and LIMIT.

Filtering and Sorting Together#

To get the biggest sales, but only in the East region:

SELECT order_id, product_id, sales
  FROM orders
 WHERE region = 'East'
 ORDER BY sales DESC;

WHERE filters first, then ORDER BY sorts what remains. Remember the clause order: WHERE always comes before ORDER BY.

Building Top-N Lists with LIMIT#

Pair ORDER BY with LIMIT to grab the top (or bottom) N rows. To find the 10 orders with the highest profit margin:

SELECT order_id, profit / sales AS profit_margin
  FROM orders
 ORDER BY profit_margin DESC
 LIMIT 10;

Three things happen: a profit_margin field is derived, the rows are sorted highest-first, and LIMIT 10 keeps only the top ten. Flip DESC to ascending for a Bottom-N list. Add a WHERE and you can answer very specific questions — for example, the top 10 largest Office Supplies orders in the Central region.

ORDER BY in the Order of Execution#

Now that you know ORDER BY, here is where it falls in SQL’s execution order:

1. FROM — pick the table
2. WHERE — filter the rows
3. SELECT — choose the columns and compute aliases
4. ORDER BY — sort the rows
5. LIMIT — trim the count

There is a helpful consequence: because ORDER BY runs after SELECT, the aliases you defined in SELECT already exist, so you can sort by an alias. That is why ORDER BY profit_margin DESC works above, even though WHERE could not use that alias. This is the key difference between the two clauses.

Coding with Style#

You may have noticed every query in this module follows the same formatting. SQL itself does not care — it runs fine unformatted — but readability matters enormously when you share queries or revisit your own work months later.

The Style Conventions#

Compare an unformatted query:

select order_id, product_name, sales/quantity price_per_unit from orders
where order_id like 'CA%' order by quantity limit 10;

with a formatted one:

SELECT order_id, product_name, sales / quantity AS price_per_unit
  FROM orders
 WHERE order_id LIKE 'CA%'
 ORDER BY quantity
 LIMIT 10;

The second is far easier to scan. The guidelines used throughout this module are:

1. Put each clause on its own line and capitalize keywords (SELECT, FROM, WHERE).
2. Write identifiers (column and table names) in lowercase.
3. Write operators like LIKE and IN in uppercase.
4. End every statement with a semicolon (;).

You may also notice the keywords are right-aligned, creating an empty vertical channel down the middle of the query. This is called a river, and it makes the structure of a query jump out at a glance. Different teams adopt different style guides, but consistency is what counts — pick clear conventions and stick to them.

Practice Exercises#

Use the orders table.

Exercise 1: Most Profitable Orders#

Write a query returning order_id, product_name, and profit, sorted so the highest-profit orders appear first.

Exercise 2: Furniture, Sorted Two Ways#

Write a query returning subcategory, sales, and profit for the Furniture category only, sorted by sales highest-to-lowest, then profit lowest-to-highest.

Exercise 3: Top 10 Cheap Units#

Write a query returning product_name and a price_per_unit field (sales / quantity), keeping only rows where price per unit is under $95, sorted to show the 10 highest such prices.

Summary#

You learned to take control of result order and trim it to exactly what you need. You sorted ascending and descending, broke ties with multiple columns, built Top-N lists by combining ORDER BY with LIMIT, and adopted the style conventions that keep SQL readable.

Key Concepts#

• ORDER BY — sorts results; ascending by default, DESC for descending; sits after WHERE, before LIMIT.
• Multiple-column sort — sorts by the first column, then breaks ties with the next; each column can have its own direction.
• Ordinal sorting — referring to columns by number works but is discouraged for readability and safety.
• ORDER BY + LIMIT — creates Top-N and Bottom-N lists.
• Order of execution — FROM, WHERE, SELECT, ORDER BY, LIMIT; aliases can be used in ORDER BY because it runs after SELECT.
• Style conventions — one clause per line, capitalized keywords, lowercase identifiers, a “river,” and a closing semicolon.

Why This Matters#

The most valuable insights are usually extremes: the biggest losses, the best customers, the slowest-moving products. Sorting and limiting are how you surface those extremes instantly instead of scrolling through thousands of rows. Every “top 10” report, every leaderboard, every “what’s our worst-performing X” question is an ORDER BY plus a LIMIT. And the formatting habits you picked up here are not cosmetic — clean, consistent queries are easier to debug, safer to edit, and far more pleasant for the next person (often future you) to read.

Next Steps#

Continue Building Your Skills#

Sorting and limiting turn a wall of data into a clear answer. Practice building Top-N lists by stacking WHERE, ORDER BY, and LIMIT together — it is one of the most-used patterns in all of analytics. Next you will add conditional logic to your toolkit and label your data on the fly.