Part 3: Mastering the ORDER BY Command | SQL Challenges & Capstone
SQL Challenges & Capstone · Part 3 · Lecture Notes
Mastering the ORDER BY Command
How to sort query results — ascending and descending order, and multi-column sorting for precise business reports.
Part 3 of 650% Complete
ORDER BY
ASC and DESC
Multi-Column Sorting
8 Practice Questions
Learning Objectives
Use ORDER BY to sort query results into a logical sequence
Apply ASC and DESC to control whether results sort low-to-high or high-to-low
Combine multiple columns in ORDER BY to create a precise, multi-level sort order
Use ORDER BY with DESC to build Top N business reports
Click any section header to open or close it
ORDER BY sorts query results into a logical sequence. Without ORDER BY, the database returns rows in no guaranteed order — it simply retrieves them as they are stored internally. ORDER BY gives you control over that sequence.
In industry, analysts use ORDER BY to rank products by price, sort transactions from largest to smallest, organize reports chronologically, review newest or oldest records first, and identify the highest or lowest values in a dataset.
The Basic Structure
ORDER BY goes at the end of the query — after FROM and after any WHERE filter. You name the column to sort by, and optionally add ASC (ascending, the default) or DESC (descending).
Basic ORDER BY Syntax
SELECT column_name
FROM table_name
ORDER BY column_name;
ASC sorts from lowest to highest — alphabetically A to Z, numerically smallest to largest, dates oldest to newest. DESC reverses this — Z to A, largest to smallest, newest to oldest. ASC is the default; if you omit the direction, SQL sorts ascending automatically.
When to Use DESC
DESC is most useful for Top N analysis — showing the highest transaction first, the most recent order, the most expensive product. Lead with DESC whenever you want the most significant records at the top of your results.
Sort Products Cheapest to Most Expensive (ASC)
SELECT *
FROM products
ORDER BY unit_price ASC;
Sort Transactions Highest to Lowest (DESC)
SELECT *
FROM transactions
ORDER BY amount DESC;
SQL can sort by more than one column. The first column listed is the primary sort. When two rows have the same value in that column, the second column determines the tie-break order. You can specify different directions for each column.
How Multi-Column Sorting Works
Think of it as sorting a list alphabetically by last name, and then by first name when two people share the same last name. Each additional column in ORDER BY only matters when the previous columns produce identical values.
Sort Users by Country, Then by Newest Signup
SELECT *
FROM users
ORDER BY country ASC, signup_date DESC;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Sort users by first_name in ascending order
AI Prompt
Write a MySQL query to show all users sorted by first_name in ascending order.
SQL Answer
SELECT *
FROM users
ORDER BY first_name ASC;
Practice 2Sort transactions from highest to lowest amount rounded to 2 decimal places
AI Prompt
Write a MySQL query to show transactions sorted by amount from highest to lowest rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount
FROM transactions
ORDER BY amount DESC;
Practice 3Sort products by unit_price in ascending order rounded to 2 decimal places
AI Prompt
Write a MySQL query to show products sorted by unit_price in ascending order rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
ORDER BY unit_price ASC;
Practice 4Sort orders by newest order_date first
AI Prompt
Write a MySQL query to show orders sorted by order_date from newest to oldest.
SQL Answer
SELECT *
FROM orders
ORDER BY order_date DESC;
Practice 5Sort users by country and first_name
AI Prompt
Write a MySQL query to show users sorted by country and first_name.
SQL Answer
SELECT *
FROM users
ORDER BY country ASC, first_name ASC;
Practice 6Sort subscriptions by monthly_fee from highest to lowest rounded to 2 decimal places
AI Prompt
Write a MySQL query to show subscriptions sorted by monthly_fee from highest to lowest rounded to 2 decimal places.
SQL Answer
SELECT
subscription_id,
ROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
ORDER BY monthly_fee DESC;
Practice 7Sort support tickets by created_date
AI Prompt
Write a MySQL query to show support tickets sorted by created_date.
SQL Answer
SELECT *
FROM support_tickets
ORDER BY created_date ASC;
Practice 8Sort transactions by transaction_type and amount rounded to 2 decimal places
AI Prompt
Write a MySQL query to show transactions sorted by transaction_type and amount rounded to 2 decimal places.
SQL Answer
SELECT
transaction_type,
ROUND(amount, 2) AS amount
FROM transactions
ORDER BY transaction_type ASC, amount DESC;
Knowledge Check
Five questions — select the best answer for each.
1. What does ORDER BY do in a SQL query?
AIt filters rows based on a condition
BIt sorts the query results in a specified order
CIt limits the number of rows returned
DIt groups records into categories
Correct. ORDER BY sorts the results of a query. It does not filter or limit rows — it only controls the order in which they appear.
Not quite. ORDER BY sorts query results — it controls the sequence in which rows are returned.
2. What does ORDER BY amount DESC do?
ASorts amounts from lowest to highest
BRemoves duplicate amounts
CSorts amounts from highest to lowest
DFilters amounts above a threshold
Correct. DESC means descending — highest value first, lowest last. This is the standard sort direction for Top N analysis.
Not quite. DESC means descending — it sorts the column from highest to lowest.
3. If you use ORDER BY without specifying ASC or DESC, what happens?
AThe query returns an error
BResults are sorted in descending order
CResults are sorted in ascending order by default
DResults are returned in a random order
Correct. ASC is the default sort direction in SQL. Omitting the direction keyword produces the same result as writing ASC explicitly.
Not quite. ASC is the default — if you don’t specify a direction, SQL sorts ascending automatically.
4. Which query sorts users by country first, then by first_name within each country?
AORDER BY first_name, country ASC
BORDER BY country ASC, first_name ASC
CORDER BY country AND first_name
DORDER BY country OR first_name ASC
Correct. The first column in ORDER BY is the primary sort. The second column only applies when the first column has identical values — perfect for alphabetical grouping within a category.
Not quite. The correct syntax is ORDER BY country ASC, first_name ASC — the primary sort comes first.
5. An analyst wants the 3 most expensive products. Which ORDER BY direction should they use?
AORDER BY unit_price ASC
BORDER BY unit_price DESC
CORDER BY unit_price RANDOM
DORDER BY unit_price HAVING > 0
Correct. DESC puts the highest prices first. Combined with LIMIT 3, this produces the top 3 most expensive products.
Not quite. To get the most expensive items first, you need DESC — it sorts from highest to lowest.
Part 3 Summary
The key ideas from this lecture — keep these in mind as you move forward.
ORDER BY organizes query results so analysts can rank, sort, and structure business data for analysis and reporting. Use ASC for lowest-to-highest or oldest-to-newest. Use DESC for highest-to-lowest or newest-to-oldest. Combine multiple columns for precise multi-level sorting.
ORDER BYASCDESCSortingMulti-Column SortRankingTop N
