Checkpoint — Sorting and limiting

Q: How do you put NULLs last in an ascending MySQL sort?

Sort on col IS NULL first: ORDER BY col IS NULL, col. The expression is 0 for values and 1 for NULLs, so real values come first, in ascending order, and NULLs follow.

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3

Quiz · 70% to clear12 q

This checkpoint covers the whole module: ORDER BY with ASC and DESC, sorting by aliases, expressions and positions, where NULLs sort in MySQL and in other databases, multi-key sorts and tie-breakers, custom orders with CASE, LIMIT and OFFSET (and MySQL's comma form), TOP and FETCH FIRST in other dialects, offset and keyset pagination, and top-N and Nth-highest queries.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

Why does a query without ORDER BY have no guaranteed row order, and why does LIMIT need one?
In ORDER BY a, b DESC, which keys are descending?
Where does MySQL put NULLs in an ascending sort, and how do you move them to the end?
Why can an alias appear in ORDER BY but not in WHERE?
What do LIMIT 5 OFFSET 10 and LIMIT 5, 10 return?
What is the offset of page p with s rows a page, and why do deep offsets get slow?
How does keyset pagination find the next page?
Why does the second-highest-salary query need DISTINCT, and how do you make it return NULL?

The three queries are a leaderboard sorted on several keys with a tie-breaker, a page from the middle of a catalogue with NULL prices sent to the end, and the third highest distinct score — NULL when there is none.

Common questions

What does MySQL's LIMIT 10, 20 return?

Twenty rows, starting with the eleventh. In the comma form the first number is the offset and the second the count, so it equals LIMIT 20 OFFSET 10.

Why do rows repeat between pages of an offset-paginated list?

Because the ORDER BY is not unique, so rows tied on the sort key can change places between the query for one page and the next, or because rows were inserted between requests. End the sort with the primary key, and use keyset pagination for data that changes.

How do you put NULLs last in an ascending MySQL sort?

Sort on col IS NULL first: ORDER BY col IS NULL, col. The expression is 0 for values and 1 for NULLs, so real values come first, in ascending order, and NULLs follow.

SQL ORDER BY and LIMIT Quiz: Sorting and Pagination Practice

Test yourself: 12 questions and three queries on SQL ORDER BY, multi-column sorts, NULL ordering, LIMIT and OFFSET, pagination and the Nth highest value.

Course: SQL study plan
Module: Sorting and limiting
Kind: Checkpoint — cleared at 70%
Reading time: 25 min
Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Sorting and limiting is the checkpoint that closes the Sorting and limiting module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

Why does a query without ORDER BY have no guaranteed row order, and why does LIMIT need one?
In ORDER BY a, b DESC, which keys are descending?
Where does MySQL put NULLs in an ascending sort, and how do you move them to the end?
Why can an alias appear in ORDER BY but not in WHERE?
What do LIMIT 5 OFFSET 10 and LIMIT 5, 10 return?
What is the offset of page p with s rows a page, and why do deep offsets get slow?
How does keyset pagination find the next page?
Why does the second-highest-salary query need DISTINCT, and how do you make it return NULL?

Common questions

What does MySQL's LIMIT 10, 20 return?

Twenty rows, starting with the eleventh. In the comma form the first number is the offset and the second the count, so it equals LIMIT 20 OFFSET 10.

Why do rows repeat between pages of an offset-paginated list?

How do you put NULLs last in an ascending MySQL sort?

Sort on col IS NULL first: ORDER BY col IS NULL, col. The expression is 0 for values and 1 for NULLs, so real values come first, in ascending order, and NULLs follow.

Exercises

Leaderboard

The players table:

id	name	points	joined_on
1	Asha	120	2024-01-05
2	Ben	150	2024-02-01
3	Chen	120	2024-01-02
4	Dina	90	2024-01-01
5	Eli	150	2024-02-01
6	Fay	60	2023-12-30
7	Gus	120	2024-01-05

Return the top five players as name, points: most points first; on equal points, the player who joined earlier first; on the same day, the lower id first.

name | points
Ben | 150
Eli | 150
Chen | 120
Asha | 120
Gus | 120

Catalogue page, unpriced items last

The items table; price is NULL for items not priced yet:

id	name	price
1	lamp	NULL
2	desk	4500
3	chair	1200
4	mat	300
5	shelf	NULL
6	bin	300
7	rug	2500

The catalogue sorts by price ascending with NULL prices at the end, then by id, three items a page. Return page 2 as id, name, price.

id | name | price
7 | rug | 2500
2 | desk | 4500
1 | lamp | NULL

Third highest distinct score

The scores table:

id	player	score
1	ana	88
2	bo	95
3	cy	88
4	dee	70
5	ed	95
6	flo	60

Return the third highest distinct score in one column named third_highest — a single row holding NULL when there are fewer than three distinct scores.

third_highest
70

In this module: Sorting and limiting

← Top-N queries and the Nth highest value · NULL and three-valued logic →