ORDER BY and LIMIT

SQL: lesson 3 of 10

Sort once in the engine, then take only the slice you show.

Lesson 3 of 10 · 4 min

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

ORDER BY and LIMIT