Lesson 3: Sorting and Pagination

In Lesson 2, we filtered rows with WHERE. But the results came in no particular order, right? You can sort with ORDER BY and retrieve only the top N rows with LIMIT.

The row order of SQL results is not guaranteed unless explicitly specified. You can sort by one or more columns using ORDER BY, and use LIMIT and OFFSET to browse large result sets page by page.

Concept: ORDER BY sorts the rows, then LIMIT clips the top N.

ORDER BY -- Single Column

Append ASC (ascending, default) or DESC (descending) after the column name.

-- Sort products from lowest price
SELECT name, price
FROM products
WHERE is_active = 1
ORDER BY price ASC;

Result:

-- Sort products from highest price
SELECT name, price
FROM products
WHERE is_active = 1
ORDER BY price DESC;

Result:

ORDER BY -- Multiple Columns

Sorts by the first column first, then by the second column when values are equal.

-- Sort by grade, then alphabetically by name within each grade
SELECT name, grade, point_balance
FROM customers
WHERE is_active = 1
ORDER BY grade ASC, name ASC;

Result:

-- Sort by most recent order first; for the same timestamp, by amount descending
SELECT order_number, ordered_at, total_amount
FROM orders
ORDER BY ordered_at DESC, total_amount DESC;

Result:

LIMIT

LIMIT n returns at most n rows. When used with ORDER BY, it meaningfully extracts the "top N" results.

-- The 5 most expensive active products
SELECT name, price
FROM products
WHERE is_active = 1
ORDER BY price DESC
LIMIT 5;

Result:

OFFSET -- Pagination

OFFSET n skips the first n rows and returns the rest. When used with LIMIT, you can implement page-based browsing.

-- Page 1: rows 1-10
SELECT name, price
FROM products
WHERE is_active = 1
ORDER BY name ASC
LIMIT 10 OFFSET 0;

-- Page 2: rows 11-20
SELECT name, price
FROM products
WHERE is_active = 1
ORDER BY name ASC
LIMIT 10 OFFSET 10;

-- Page 3: rows 21-30
SELECT name, price
FROM products
WHERE is_active = 1
ORDER BY name ASC
LIMIT 10 OFFSET 20;

Page 1 result:

Formula: OFFSET = (page number - 1) x page size

NULL Sort Order

In SQLite, NULLs come before other values with ASC sorting and after with DESC sorting.

-- When sorting birth_date ascending, NULLs appear first
SELECT name, birth_date
FROM customers
ORDER BY birth_date ASC
LIMIT 5;

Result:

Summary

Practice Problems

Problem 1

Find the 10 most recently placed orders. Return order_number, ordered_at, status, and total_amount.

SELECT order_number, ordered_at, status, total_amount
FROM orders
ORDER BY ordered_at DESC
LIMIT 10;

Problem 2

Sort all products by stock_qty ascending (lowest stock first); for equal stock, sort by price descending. Return name, stock_qty, and price, limited to 20 rows.

SELECT name, stock_qty, price
FROM products
ORDER BY stock_qty ASC, price DESC
LIMIT 20;

Problem 3

Retrieve page 3 (10 items per page) of the active product catalog sorted alphabetically by product name.

SELECT name, price, stock_qty
FROM products
WHERE is_active = 1
ORDER BY name ASC
LIMIT 10 OFFSET 20;

Problem 4

Retrieve the name, grade, and point_balance of the top 5 customers with the most points from the customers table.

SELECT name, grade, point_balance
FROM customers
ORDER BY point_balance DESC
LIMIT 5;

Problem 5

Retrieve name and price from the products table sorted by price ascending. For equal prices, sort by product name alphabetically.

SELECT name, price
FROM products
ORDER BY price ASC, name ASC;

Problem 6

Retrieve name, price, and cost_price from the products table, sorted by margin (price - cost_price) in descending order. Return only the top 10.

SELECT name, price, cost_price
FROM products
ORDER BY price - cost_price DESC
LIMIT 10;

Problem 7

Retrieve product_id, rating, and created_at from the reviews table, sorted by most recent review first, returning the 6th through 10th reviews (page 2, 5 per page).

SELECT product_id, rating, created_at
FROM reviews
ORDER BY created_at DESC
LIMIT 5 OFFSET 5;

Problem 8

Retrieve name, department, and hired_at from the staff table. Sort by department name alphabetically; within the same department, sort by hire date with longest-tenured employees first.

SELECT name, department, hired_at
FROM staff
ORDER BY department ASC, hired_at ASC;

Problem 9

Retrieve name and birth_date from the customers table, with customers whose birth date is NULL appearing at the very end. Non-NULL customers should be sorted by birth date ascending.

SELECT name, birth_date
FROM customers
ORDER BY birth_date IS NULL ASC, birth_date ASC;

SELECT name, birth_date
FROM customers
ORDER BY birth_date IS NULL ASC, birth_date ASC;

SELECT name, birth_date
FROM customers
ORDER BY birth_date ASC NULLS LAST;

Problem 10

Retrieve order_number, total_amount, and ordered_at from the orders table. Sort by order amount descending; for equal amounts, most recent order first. Return only the top 15.

SELECT order_number, total_amount, ordered_at
FROM orders
ORDER BY total_amount DESC, ordered_at DESC
LIMIT 15;

Scoring Guide

Problem Areas:

Next: Lesson 4: Aggregate Functions