Learning Goals
3 min- Sort rows with
ORDER BY col; ascending and descending. - Sort by multiple columns — primary then tiebreaker.
- Take the top N with
LIMIT N. - Skip rows with
OFFSET— pagination.
Warm-Up · The Order Matters
5 minUntil now, SQL has returned rows in whatever order it pleases. That works for filters but is hopeless for leaderboards. ORDER BY fixes it.
The clause order of a query: SELECT cols FROM table WHERE filter ORDER BY col LIMIT N OFFSET M;
Sort first, then slice. ORDER BY arranges; LIMIT picks how many. Together they turn data into rankings.
New Concept · Sort + Slice
14 minORDER BY
SELECT name, score FROM students ORDER BY score; -- ascending (default) SELECT name, score FROM students ORDER BY score DESC; -- descending SELECT name, age FROM students ORDER BY age ASC, name ASC; -- two keys
With multiple sort keys, SQL sorts by the first one; ties are broken by the second.
LIMIT — take only the top few
-- top 3 scorers SELECT name, score FROM students ORDER BY score DESC LIMIT 3;
name score Anita 95 Suresh 92 Aisyah 88
OFFSET — skip rows (pagination)
-- 4th to 6th place SELECT name, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 3;
name score Mei 80 Wei Jie 75 Ali 70
The classic pagination pattern: page n with page size p is LIMIT p OFFSET (n-1)*p.
Combine with WHERE
-- top 2 scorers in Form 1A SELECT name, score FROM students WHERE class = 'Form 1A' ORDER BY score DESC LIMIT 2;
Sort by an expression
You can sort by anything you can compute — not just columns. For example, by name length:
SELECT name FROM students ORDER BY LENGTH(name) DESC LIMIT 3;
Worked Example · Build a Leaderboard
12 min-- All students ranked SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank, name, class, score FROM students ORDER BY score DESC;
rank name class score 1 Anita Form 1A 95 2 Suresh Form 1B 92 3 Aisyah Form 1A 88 4 Mei Form 1B 80 5 Wei Jie Form 1A 75 6 Ali Form 1B 70 7 Devi Form 1A 65 8 Razif Form 1B 55
ROW_NUMBER() OVER (...) assigns 1, 2, 3 in the order specified. Window functions like this are a Level-4 stretch — feel free to skip the rank column if it's confusing.
Top per class
SELECT * FROM students WHERE class = 'Form 1A' ORDER BY score DESC LIMIT 1; -- Anita (95) SELECT * FROM students WHERE class = 'Form 1B' ORDER BY score DESC LIMIT 1; -- Suresh (92)
Bottom of the class
SELECT name, score FROM students ORDER BY score ASC LIMIT 3;
name score Razif 55 Devi 65 Ali 70
Read the diff
Same data, three different stories — top 3, top per class, bottom 3 — just by changing one clause. Sorting + limiting is the cheapest way to turn data into reports.
Try It Yourself
13 minList every student by name alphabetically.
Hint
SELECT name FROM students ORDER BY name;
Sort by age descending. Break ties with score ascending.
Hint
SELECT name, age, score FROM students ORDER BY age DESC, score ASC;
Show students 4–6 on the leaderboard (sort by score descending, skip 3, take 3).
Hint
SELECT name, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 3;
Mini-Challenge · Hall of Fame
8 minWrite a single query that prints the top scorer from each class. (Two SELECTs joined with UNION ALL is one fair way; window functions like ROW_NUMBER() OVER (PARTITION BY ...) are the slicker way.)
Show two possible solutions
-- Way A: UNION ALL (SELECT * FROM students WHERE class='Form 1A' ORDER BY score DESC LIMIT 1) UNION ALL (SELECT * FROM students WHERE class='Form 1B' ORDER BY score DESC LIMIT 1); -- Way B: window function SELECT name, class, score FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rn FROM students ) WHERE rn = 1;
Way B scales — it works for any number of classes. Window functions are a strong tool to add to your kit.
Recap
3 minThe clause order is fixed: SELECT … FROM … WHERE … ORDER BY … LIMIT … OFFSET. ORDER BY can take multiple columns and DESC for descending. LIMIT + OFFSET = pagination. Tomorrow we group and aggregate.
Vocabulary Card
- ORDER BY
- Sort the result rows. Default is ascending.
- DESC / ASC
- Direction — descending / ascending.
- LIMIT
- Return at most N rows.
- OFFSET
- Skip the first M rows before applying LIMIT.
Homework
4 minAdd a price column to students? No — make a NEW table books with id, title, author, price, year. Insert 10 rows. Then write 5 queries:
- Top 3 most expensive books.
- Cheapest book.
- Books published before 2010 — newest first.
- Page 2 of an alphabetical list (page size 3).
- Top 2 books per author by year. (Use a window function if you can.)
SELECT title, price FROM books ORDER BY price DESC LIMIT 3; SELECT title, price FROM books ORDER BY price ASC LIMIT 1; SELECT title, year FROM books WHERE year < 2010 ORDER BY year DESC; SELECT title FROM books ORDER BY title LIMIT 3 OFFSET 3; SELECT title, author, year FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY author ORDER BY year DESC) AS rn FROM books ) WHERE rn <= 2;