Sorting & Limiting

ORDER BY - Sorting Results

The ORDER BY clause sorts the results of a query by one or more columns. By default, results are sorted in ascending order (smallest to largest, A to Z). You can explicitly specify ASC for ascending or DESC for descending.

You can sort by multiple columns — the database sorts by the first column, then uses the second column to break ties.

• ASC: Ascending order (default) — A to Z, 1 to 100, oldest to newest
• DESC: Descending order — Z to A, 100 to 1, newest to oldest
• Multiple columns: ORDER BY column1 ASC, column2 DESC

-- Sort users by age (youngest first)
SELECT * FROM users ORDER BY age ASC;

-- Sort users by age (oldest first)
SELECT * FROM users ORDER BY age DESC;

-- Sort by multiple columns
SELECT * FROM users
ORDER BY is_active DESC, username ASC;

-- Sort by join date, newest first
SELECT username, join_date FROM users
ORDER BY join_date DESC;

LIMIT, OFFSET & Pagination

LIMIT restricts the number of rows returned by a query. OFFSET skips a specified number of rows before starting to return results. Together, they are used to implement pagination — showing data in pages.

Pagination is essential for web applications. Instead of loading thousands of records at once, you load them in manageable chunks (e.g., 10 or 20 per page).

-- Get only the first 5 users
SELECT * FROM users LIMIT 5;

-- Get the 3 oldest users
SELECT * FROM users ORDER BY age DESC LIMIT 3;

-- Pagination: Page 1 (first 10 results)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;

-- Pagination: Page 2 (next 10 results)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- Pagination: Page 3
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- Formula: OFFSET = (page_number - 1) * page_size