🏆 Introduction: The Arcade Machine
Imagine you are playing an arcade racing game. When the game ends, it displays the Top 5 High Scores:
- The Order (ORDER BY): The scores aren't listed in the order they were achieved. The computer sorts them from highest to lowest (Descending order).
- The Cutoff (LIMIT): Even if 1,000,000 games have been played, the screen has only enough room to show exactly 5 rows.
- The Next Page (OFFSET): If you click "Next Page", the game skips the top 5 scores (OFFSET 5) and displays ranks 6 to 10.
In SQL, this is exactly how we use ORDER BY, LIMIT, and OFFSET! They clean up our lists and let us paginate through massive sets of rows.
🛠️ The Syntax and Options
Unsorted Rows
|
v
+----------------------+
| ORDER BY score | <--- Sorts rows (e.g. score DESC)
+----------------------+
|
Sorted Rows
|
v
+----------------------+
| LIMIT 5 OFFSET 5 | <--- Skips first 5, grabs next 5
+----------------------+
|
v
Output Rows
1. Sorting: ORDER BY
Sorts the output rows based on one or more columns.
ASC: Sorts ascending (A to Z, 1 to 10). This is the default if you omit it.DESC: Sorts descending (Z to A, 10 to 1).
2. Truncating: LIMIT
Caps the maximum number of rows returned by the query.
3. Paging: OFFSET
Skips a specific number of rows before it starts returning results.
- Formula: To get page $P$ with size $S$, use:
LIMIT S OFFSET (P - 1) * S.
💻 Code Examples
Let's retrieve the top 3 highest-scoring players from our leaderboard table.
SQL Query
-- Get the top 3 scores, sorted highest first
SELECT username, score
FROM leaderboard
ORDER BY score DESC
LIMIT 3;Multi-Language Execution
⚠️ Common Mistakes
1. OFFSET without ORDER BY
Using LIMIT 5 OFFSET 5 without specifying an ORDER BY. Without sorting, the database order is unpredictable, and you might get duplicate or missing items across pages!
- Bad:
SELECT * FROM users LIMIT 10 OFFSET 10;(Unstable result). - Good:
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;(Stable result).
2. Slow Offsets (Deep Pagination)
Using a massive OFFSET (like OFFSET 1000000). The database still has to read and discard 1,000,000 rows, which makes the query very slow. Use keyset pagination (cursor-based paging) for huge lists!
🔍 Interview Corner
Q1: What is the difference between OFFSET pagination and Keysets (Cursor) pagination?
- OFFSET pagination works by skipping $N$ rows. It is simple but gets slower as $N$ grows because the database must scan all skipped rows.
- Keyset pagination works by filtering on a unique sorted key (e.g.
WHERE id > last_seen_id LIMIT 10). It is extremely fast ($O(log N)$ with index) and does not degrade at scale.
Q2: How do you sort by multiple columns?
You can pass multiple columns separated by commas to the ORDER BY clause. The database sorts by the first column first, and then resolves ties using the second column:
SELECT name, department, score FROM employees ORDER BY department ASC, score DESC;📝 Practice Links
Explore related coding challenges on the platform:
📝 Summary
- Use
ORDER BY column [ASC/DESC]to sort your data. - Use
LIMIT nto cap the maximum number of rows. - Use
OFFSET nto skip rows, which is essential for list pagination.