SQL chapters

Chapter 16 of 17

Query order, sorting and limits

Try this in our SQLite Compiler →

The idea

Write SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT in their grammar order. Results have no guaranteed order unless ORDER BY is present. If several rows tie, add a secondary key for deterministic ordering. LIMIT alone does not mean highest or latest.

Worked example

WITH scores(name, score) AS (VALUES ('Asha', 82), ('Ravi', 60), ('Mina', 82))
SELECT name, score FROM scores WHERE score >= 70 ORDER BY score DESC, name ASC LIMIT 2;

What to check

Asha and Mina appear in name order within the tied score of 82. The secondary sort makes this predictable.

Practice task

Add a student scoring 95. Predict the first two rows, then run the query to confirm.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on