2 · Handle real values · 12 MIN

Sort ties and choose a small result

A stable order needs an explicit tie-breaker.

ORDER BY points DESC puts larger scores first. Ada and Sam tie at 20, so id ASC breaks that tie with a stable second key. LIMIT keeps only the requested number of sorted rows in SQLite. Without an explicit tie-breaker the database may return tied rows in either order, which makes top lists and pagination confusing. SELECT column order controls the shape of each row; ORDER BY controls the order among rows.

Keep the source rows visible. Predict which rows and columns will remain after each operation.

Before you start

No installation. The tables below are loaded automatically for every run; edits do not touch the CodingNeed account database.

New words, explained

DESC: Descending: larger values first.
ASC: Ascending: smaller values first.
tieBreaker: A second ordering field used when the first is equal.

Follow the example step by step

Sort all four rows on paper by points.
Resolve the 20-point tie by id.
Only then keep the first two rows.
Reverse the primary direction for the exercise without changing its tie-breaker.

Practice table: learners
id	name	city	points
1	Ada	Delhi	20
2	Lin	NULL (unknown)	0
3	Grace	Delhi	35
4	Sam	Pune	20

Practice table: enrollments
learner_id	course
1	React
1	SQL
3	SQL

You are ready to move on when: Return the two lowest-scoring learners as name, points. Sort points ascending, then id ascending.

Read the example

SELECT name, points FROM learners
ORDER BY points DESC, id ASC
LIMIT 2;

Check the expected output

[["Grace",35],["Ada",20]]

Your challenge

Return the two lowest-scoring learners as name, points. Sort points ascending, then id ascending.

Common trap

LIMIT without ORDER BY does not define which rows are first.

Open interactive exercise →See the syllabus

Further reading: SQLite SELECT reference

Next lesson: Count rows before grouping them →