QUERYINGBEGINNERlesson-06

Sorting & Limiting Results

🕑 20 min

📃 4 sections

🎓 Querying

Without ORDER BY, SQL results come back in an unpredictable order — the database returns rows however it finds them fastest. ORDER BY guarantees a consistent sequence, and LIMIT gives you top-N reports, latest records, and pagination. These two clauses are behind every leaderboard and feed in every app.

Section 1

ORDER BY

SQL

-- Ascending (A to Z, 0 to 9) — default
SELECT name, age FROM students ORDER BY age;
SELECT name, grade FROM students ORDER BY grade ASC;

-- Descending (Z to A, 9 to 0)
SELECT name, age FROM students ORDER BY age DESC;

-- Multiple sort columns (primary sort, then tiebreaker)
SELECT name, grade, age
FROM students
ORDER BY grade ASC, age DESC;

-- Sort by column position (column 2 = age)
SELECT name, age FROM students ORDER BY 2 DESC;

-- Sort by calculated expression
SELECT name, age FROM students ORDER BY (age * 2) DESC;

Section 2

LIMIT and OFFSET

SQL

-- Top 5 students by grade
SELECT name, grade FROM students ORDER BY grade ASC LIMIT 5;

-- Skip first 10, get next 10 (page 2 of a paginated list)
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 10;

-- Equivalent shorthand: LIMIT offset, count
SELECT * FROM students ORDER BY id LIMIT 10, 10;  -- skip 10, get 10

-- Get the single latest record
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1;

💡

Always Pair LIMIT with ORDER BY

LIMIT without ORDER BY returns random rows. Always combine them for predictable results.

Section 3

Real-World Patterns

SQL

-- Leaderboard: top 10 scores
SELECT player_name, score
FROM game_scores
ORDER BY score DESC
LIMIT 10;

-- Latest 5 orders
SELECT order_id, customer_name, total
FROM orders
ORDER BY order_date DESC
LIMIT 5;

-- Pagination for a web app (page N, 20 items per page)
-- Page 1: LIMIT 20 OFFSET 0
-- Page 2: LIMIT 20 OFFSET 20
-- Page N: LIMIT 20 OFFSET ((N-1) * 20)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;  -- page 3

OFFSET for page 1

OFFSET for page 2

OFFSET for page 3

items per page

Section 4

NULL Handling in ORDER BY

SQL

-- NULLs sort FIRST in ASC, LAST in DESC (MySQL default)
SELECT name, email FROM students ORDER BY email ASC;
-- Students with no email appear first

-- Force NULLs to the end in ASC sort
SELECT name, email
FROM students
ORDER BY email IS NULL ASC, email ASC;
-- 'email IS NULL' = 0 for real values, 1 for NULLs
-- ASC puts 0s first (real values first)

🎤 Knowledge Check

1. What does ORDER BY salary DESC return?

DESC = descending = largest/highest first. So ORDER BY salary DESC puts the highest-paid employee at the top.

2. How do you get rows 21 to 30?

Skip 20 rows (OFFSET 20), then take 10 (LIMIT 10). This gives rows 21-30.

3. Default sort direction in ORDER BY?

ASC (ascending) is the default. A to Z for text, 0 to 9 for numbers. You only need to write DESC explicitly.

4. LIMIT without ORDER BY returns?

Without ORDER BY, the database can return any N rows it finds fastest. The result is non-deterministic — it can change between runs.

5. Page 4 with 10 items per page — correct LIMIT/OFFSET?

Page 4 = skip pages 1, 2, 3 = skip (4-1) x 10 = 30 rows. So LIMIT 10 OFFSET 30.