Sorting and Pagination

ORDER BY and LIMIT clauses are used to sort query results and limit the number of rows returned. This chapter introduces how to use these clauses.

ORDER BY Sorting

Basic Syntax

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC];

Ascending Order (Default)

-- Sort by age ascending
SELECT name, age
FROM users
ORDER BY age;

-- Or explicitly specify ASC
SELECT name, age
FROM users
ORDER BY age ASC;

Descending Order

-- Sort by age descending
SELECT name, age
FROM users
ORDER BY age DESC;

Multi-Column Sorting

-- Sort by city first, then by age
SELECT name, city, age
FROM users
ORDER BY city ASC, age DESC;

Sort by Expression

-- Sort by calculated result
SELECT name, price, quantity, price * quantity AS total
FROM order_items
ORDER BY price * quantity DESC;

-- Sort by string length
SELECT name
FROM users
ORDER BY LENGTH(name);

NULL Value Sorting

-- NULL values default to last (ASC) or first (DESC)
SELECT name, email
FROM users
ORDER BY email;

-- Force NULL values first
SELECT name, email
FROM users
ORDER BY email IS NULL DESC, email;

LIMIT Results

Basic Syntax

SELECT column1, column2
FROM table_name
LIMIT number;

Limit Rows

-- Return only first 5 records
SELECT name, age
FROM users
LIMIT 5;

OFFSET Skip Rows

-- Skip first 10, return next 5
SELECT name, age
FROM users
LIMIT 5 OFFSET 10;

-- MySQL shorthand
SELECT name, age
FROM users
LIMIT 10, 5;  -- LIMIT offset, count

Pagination

Calculate Pagination Parameters

-- Page 1 (10 per page)
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

-- Page 2
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 10;

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

Pagination Formula

OFFSET = (page_number - 1) * page_size
LIMIT = page_size

TOP N Queries

Query Top N Records

-- Query 5 oldest users
SELECT name, age
FROM users
ORDER BY age DESC
LIMIT 5;

-- Query top 10 best-selling products
SELECT name, sales_count
FROM products
ORDER BY sales_count DESC
LIMIT 10;

Practical Examples

Product List Pagination

-- Product list, sorted by sales, page 2
SELECT 
    id,
    name,
    price,
    sales_count
FROM products
WHERE is_active = TRUE
ORDER BY sales_count DESC, id ASC
LIMIT 20 OFFSET 20;

Latest Orders

-- Get latest 10 orders
SELECT 
    order_number,
    user_id,
    total_amount,
    created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Leaderboard

-- Top 20 users by spending
SELECT 
    u.username,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_spent DESC
LIMIT 20;

Performance Optimization

1. Create Indexes for Sort Columns

-- Create index for commonly sorted columns
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_created_at ON orders(created_at);

2. Avoid Deep Pagination

-- Not recommended: Deep pagination performs poorly
SELECT * FROM users
ORDER BY id
LIMIT 20 OFFSET 100000;

-- Recommended: Use WHERE condition
SELECT * FROM users
WHERE id > 100000
ORDER BY id
LIMIT 20;

Different Database Syntax

MySQL

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

SQL Server

-- SQL Server 2012+
SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Oracle

-- Oracle 12c+
SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Best Practices

1. Always use ORDER BY with pagination
2. Create indexes for sort columns
3. Avoid deep pagination
4. Use stable sort columns (like primary key)
5. Consider cursor-based pagination instead of OFFSET

Summary

• ORDER BY: Sort results
• ASC/DESC: Ascending/descending
• LIMIT: Limit number of rows
• OFFSET: Skip specified rows
• Pagination: LIMIT + OFFSET for pagination
• Performance: Create indexes, avoid deep pagination

Next Step: Learn GROUP BY