ORDER BY and LIMIT
SQL: lesson 3 of 15
Sort once in the engine, then take only the slice you show.
Lesson 3 of 15 · 4 min
ORDER BY and LIMIT
Step 1 of 9
With no ORDER BY, rows arrive in whatever order the engine found convenient. No promise.
The Idea
ORDER BY sorts the result; LIMIT cuts it short. Together they answer every "top N" question in one round trip, and the engine can often stop early instead of sorting everything. Alone, LIMIT is a trap: with no ORDER BY, the rows you keep are simply the ones that arrived first.
Real-World Example
A race timing system reads thousands of chip crossings, but the podium board needs three names. The clock sorts by elapsed time and prints the first three — nobody prints the full field to find the winner.
The Code
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE runner (bib INTEGER, name TEXT, seconds INTEGER);
INSERT INTO runner VALUES (14, 'Ada', 9120), (7, 'Bruno', 8755),
(22, 'Chi', 9040), (3, 'Dara', 8890);
""")
podium = db.execute("""
SELECT name, seconds FROM runner
ORDER BY seconds ASC
LIMIT 3
""").fetchall()
print(podium)
# [('Bruno', 8755), ('Dara', 8890), ('Chi', 9040)]Your turn
Put the steps in the right order.
- Hand back the first 3 rows and stop
- Sort the survivors by elapsed time, smallest first
- Read the rows of the runner table
- Drop runners who never crossed the line
Mini quiz
1 / 3