Sort and limit results

Use ORDER BY and LIMIT with deterministic tie-breakers to select the top results.

About 7 minutes · Beginner

Define an order

ORDER BY score DESC puts larger scores first. ASC puts smaller values first and is the default.

When scores tie, a second sort column makes the result deterministic. A unique id is a useful final tie-breaker.

SELECT name, score
FROM students
ORDER BY score DESC, id ASC;

Expected output

Meera | 85
Asha | 72
Ravi | 38

LIMIT caps the number of returned rows. Combine it with ORDER BY whenever “first” or “top” has a specific meaning.

OFFSET skips a number of sorted rows. Large offsets can be expensive; larger applications often use a last-seen key to fetch the next page.

SELECT name, score
FROM students
ORDER BY score DESC, id ASC
LIMIT 2;

Expected output

Meera | 85
Asha | 72

A table can appear to return rows in insertion order during a small test. That is not a contract and can change with indexes or query plans.

Always specify the ordering required by the interface rather than depending on the storage engine’s current behavior.

Return the lowest-scoring student, breaking any tie by id.

Show a possible solution

SELECT name, score FROM students ORDER BY score ASC, id ASC LIMIT 1;

0 of 5 completed on this device