Sql · Sorting and Limiting Results
LIMIT and OFFSET
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — LIMIT with ORDER BY
The
LIMIT
clause is most useful when combined with
ORDER BY
. This allows you to retrieve a specific number of rows in a meaningful order, such as the top N highest values or the N most recent records.
The correct order of clauses is:
SELECT columns
FROM table
WHERE condition
ORDER BY columns
LIMIT number
Let's see an example to find the 3 most powerful Codémons:
SELECT name, type, power
FROM codemon
ORDER BY power DESC
LIMIT 3;
This query first sorts all Codémons by power in descending order, then returns only the top 3 results (the 3 most powerful Codémons).
You can also combine LIMIT with WHERE to filter and limit in the same query:
SELECT name, type, level
FROM codemon
WHERE type IN ('fire', 'water')
ORDER BY level DESC
LIMIT 1;
Always Use ORDER BY with LIMIT
Without ORDER BY, the rows returned by LIMIT will be unpredictable. Always include an ORDER BY clause when using LIMIT if you want consistent, meaningful results.
Exercise 2 — Understanding LIMIT Behavior
Given the following query, how many rows will be returned at maximum?
SELECT name, power FROM codemon WHERE type = 'water' LIMIT 2;
Options
• Might be 0, 1, or 2 depending on the number of water-type Codémons
• All water-type Codémons
• The 2 most powerful water-type Codémons
• Exactly 2 rows, regardless of the WHERE condition
Hint : Think about what LIMIT does to the result set, can it limit the number of rows even if the WHERE condition matches less rows?
Exercise 3 — Introduction to OFFSET
The
OFFSET
clause is used to skip a specific number of rows before starting to return results. It's especially useful for pagination — like showing page 2, 3, etc. of your data.
🛑 Important: Most SQL databases require that you use
OFFSET
together with
LIMIT
(or FETCH). Using
OFFSET
alone will either not work or behave unpredictably. In this app, using
OFFSET
alone will not work and return an error.
SELECT column1, column2, ...
FROM table_name
ORDER BY some_column
LIMIT number_of_rows OFFSET number_to_skip;
Let's see an example where we skip the first 2 Codémons and show the next 3:
SELECT name, type, power
FROM codemon
ORDER BY power DESC
LIMIT 3 OFFSET 2;
This query skips the first 2 rows (Rockachu and Pyrochu with 85 and 80 of power) and returns the next 3 Codémons.
Exercise 4 — Complete the OFFSET Query
Complete the SQL query to skip the first 3 Codémons and show all the rest, ordered by level in ascending order.
SELECT name, type, level
FROM codemon
____ BY level ASC
____ ____
Options: ORDER SORT OFFSET LIMIT 3 2
Hint : ORDER BY and OFFSET are used to control the order of results and skip a specific number of rows.
Exercise 5 — Which Codémons Will Be Returned?
Given the Codémon table and the following query, which Codémons will be returned?
SELECT name, power FROM codemon ORDER BY power DESC LIMIT 2 OFFSET 1;
Options
• Pyrochu and Aquachu
• Rockachu and Pyrochu
• Aquachu and Rockachu
• Pyrochu and Florachu
Hint : Think about the order of operations: first data is sorted by power in descending order, then we skip the first row (OFFSET 1), then we take 2 rows (LIMIT 2).
Exercise 6 — Create a Top Codémons Query
Complete the SQL query to find the 3 most powerful Codémons, but excluding the very top (most powerful) one.
SELECT name, type, power
FROM codemon
ORDER BY power ____
____ 3 ____ 1
Options: DESC ASC LIMIT TOP OFFSET SKIP
Hint : To find the most powerful Codémons, you need to sort by power in descending order. Then use LIMIT to take only 3 and OFFSET to skip the first one.
Exercise 7 — Match Queries with Their Purpose
Match each SQL query with what it accomplishes:
Items to match
• SELECT name FROM codemon ORDER BY power DESC LIMIT 1;
• SELECT name FROM codemon ORDER BY level ASC LIMIT 2;
• SELECT * FROM codemon ORDER BY name LIMIT 2 OFFSET 3;
• SELECT type FROM codemon ORDER BY captured_date DESC LIMIT 3;
Definitions
• Find the name of the most powerful Codémon
• Find the names of the 2 lowest level Codémons
• Find the 4th and 5th Codémons in alphabetical order
• Find the types of the 3 most recently captured Codémons
Exercise 8 — Finding the Middle-Ranked Codémon
If you want to find the middle-ranked Codémon by power (the one that's exactly in the middle of the power ranking), which query would you use? Assume there are 5 Codémons in total.
Options
• ```SELECT name FROM codemon ORDER BY power DESC LIMIT 1 OFFSET 2;```
• ```SELECT name FROM codemon ORDER BY power DESC LIMIT 3;```
• ```SELECT name FROM codemon ORDER BY power ASC LIMIT 1 OFFSET 1;```
• ```SELECT name FROM codemon WHERE power = AVG(power);```
Hint : To find the middle element in a sorted list of 5 items, you need to get the 3rd element (index 2 when starting from 0). Think about how to skip the first 2 and limit to just 1 result.