Sql · Sorting and Limiting Results
Sorting with ORDER BY
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Introduction to Sorting with ORDER BY
The
ORDER BY
clause in SQL allows you to sort your query results in a specific order. By default, results from a
SELECT
query are returned in no particular order.
Here's the basic syntax of
ORDER BY
:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name;
Let's try sorting our Codémon by their power:
SELECT name, type, power FROM codemon ORDER BY power;
By default,
ORDER BY
sorts in ascending order (smallest to largest, A to Z). Notice how the Codémon are now arranged from lowest power to highest power.
ORDER BY Position in Query
ORDER BY always comes after the FROM clause (and any WHERE clause if present), but before LIMIT if you're using it.
Exercise 2 — Ascending and Descending Order
You can specify the direction of sorting using the keywords
ASC
(ascending) or
DESC
(descending):
-
ASC
: Sorts from smallest to largest or A to Z (default if not specified)
-
DESC
: Sorts from largest to smallest or Z to A
-- Ascending order (same as default)
SELECT name, level FROM codemon ORDER BY level ASC;
Now let's try descending order (highest to lowest):
-- Descending order
SELECT name, level FROM codemon ORDER BY level DESC;
You can also sort by text columns. Let's sort Codémons by their names:
-- Alphabetical order (A to Z)
SELECT name, type FROM codemon ORDER BY name ASC;
Remember
ASC is optional since it's the default, but it's good practice to include it to make your intentions clear, especially in complex queries.
Exercise 3 — Default Sorting Order
If you don't specify ASC or DESC in an ORDER BY clause, what is the default sorting behavior?
Options
• Ascending order (smallest to largest, A to Z)
• Descending order (largest to smallest, Z to A)
• Random order
• The order depends on the database system being used
Hint : Think about what happens when you simply write ORDER BY without any additional keywords.
Exercise 4 — Sorting by Multiple Columns
You can sort by multiple columns in SQL by listing them in the
ORDER BY
clause, separated by commas. The sorting priority follows the order of the columns listed.
The database will first sort by the first column. If there are ties (same values), it will then sort by the second column, and so on.
SELECT name, level, power FROM codemon ORDER BY level, power DESC;
In this example, the results are first grouped by level (ascending), and then within each level, Codémons are sorted by power in descending order.
You can also specify different sort directions for each column:
SELECT name, level, power FROM codemon ORDER BY level DESC, power ASC;
Real-World Application
Multi-column sorting is useful for scenarios like leaderboards (sort by score DESC, then by time ASC) or contact lists (sort by last name, then by first name).
Exercise 5 — Complete the Multi-Column Sorting
Complete the SQL query to sort Codémons first by their type in alphabetical order, and then by their power in descending order (highest power first).
SELECT name, type, power
FROM codemon
ORDER BY ____ ASC, ____ ____
Options: type name power level ASC DESC
Hint : Consider which column should be sorted first and which should be sorted second. Also think about which direction (ASC or DESC) is needed for each column.
Exercise 6 — Combining ORDER BY with WHERE
You can combine
ORDER BY
with
WHERE
to filter records first and then sort the filtered results. This is a common pattern in SQL queries.
The correct order of clauses is:
SELECT columns
FROM table
WHERE condition
ORDER BY columns
Let's see an example where we filter Codémons with power greater than 70 and then sort them by their level in descending order.
SELECT name, type, power, level
FROM codemon
WHERE power > 70
ORDER BY level DESC;
Like we saw in the previous lessons, you can add multiple conditions in the
WHERE
clause and still use
ORDER BY
:
SELECT name, type, power
FROM codemon
WHERE type = 'water' OR type = 'electric'
ORDER BY power DESC;
Order of Operations
Always remember that WHERE is processed before ORDER BY. The database first filters the data, then sorts the filtered results.
Exercise 7 — Query with Filtering and Sorting
Which query would return the names of all Codémons with level greater than 15, sorted alphabetically by name?
Options
• ```SELECT name FROM codemon WHERE level > 15 ORDER BY name ASC;```
• ```SELECT name FROM codemon ORDER BY name ASC WHERE level > 15;```
• ```SELECT name FROM codemon WHERE name = name AND level > 15;```
• ```SELECT name ORDER BY name ASC FROM codemon WHERE level > 15;```
Hint : Remember the correct order of SQL clauses in a query: SELECT, FROM, WHERE, then ORDER BY.
Exercise 8 — Column Positions in ORDER BY
In addition to using column names, you can also sort by column position in the
ORDER BY
clause. Given the following query, what would happen?
SELECT name, power, level FROM codemon ORDER BY 2 DESC;
Options
• Results would be sorted by power in descending order
• Results would be sorted by level in descending order
• Results would be sorted by name in descending order
• The query would produce an error
Hint : In ORDER BY, you can use numbers to refer to columns by their position in the SELECT list (starting from 1). Consider which column is in position 2 in this query.
Exercise 9 — Find the Most Powerful Codémons by Type
Complete the SQL query to find all fire and water type Codémons, sorted by their power in descending order.
SELECT name, type, power
FROM codemon
WHERE type = ____ OR type = 'water'
____ BY power ____
Options: 'fire' 'water' 'electric' ORDER SORT ASC DESC
Hint : You need to correctly specify the types to filter for, the ORDER BY clause, and the direction of sorting.
Exercise 10 — Sorting Expressions and Results
Match each ORDER BY expression with its correct sorting result:
Items to match
• ORDER BY name ASC
• ORDER BY power DESC
• ORDER BY type, level ASC
• ORDER BY captured_date DESC
Definitions
• Codémons sorted alphabetically (A to Z)
• Codémons sorted from highest to lowest power
• Codémons grouped by type, then by increasing level
• Codémons sorted from newest to oldest capture date
Exercise 11 — SQL Clause Execution Order
What is the correct order of execution for these SQL clauses?
We are talking about EXECUTION order, not the order they appear in the query!
Options
• FROM → WHERE → SELECT → ORDER BY
• SELECT → FROM → WHERE → ORDER BY
• FROM → SELECT → WHERE → ORDER BY
• SELECT → FROM → ORDER BY → WHERE
Hint : Understanding the actual execution order of SQL clauses is important for writing effective queries. Think about what must happen first logically.