ORDER BY, LIMIT, OFFSET

ORDER BY

The ORDER BY clause sorts the result set by one or more columns.

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;  -- Ascending order (default)

ASC vs DESC

-- Aphabetical order
SELECT * FROM employees
ORDER BY last_name ASC;

-- Salary from highest to lowest
SELECT * FROM employees
ORDER BY salary DESC;

-- Most recent orders first
SELECT * FROM orders
ORDER BY order_date DESC;

Multi-Column Sorting

-- Sort by department, then by salary within each department
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

Sort by Column Position

-- Sort by second column
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC;  -- Sorts by salary

💡 Sorting by column position is shorthand but reduces readability. Use column names for clarity, especially in production code.

Sort by Expression

-- Sort by calculated value
SELECT name, price, price * quantity AS total_value
FROM products
ORDER BY total_value DESC;

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

LIMIT

The LIMIT clause restricts the number of rows returned.

-- Get top 5 highest-paid employees
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Get first 10 customers
SELECT * FROM customers
ORDER BY created_at
LIMIT 10;

OFFSET

The OFFSET clause skips a specified number of rows before returning results.

-- Skip first 10 rows, return next 10
SELECT * FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 10;

-- Alternative syntax (MySQL)
SELECT * FROM employees
ORDER BY employee_id
LIMIT 10, 10;  -- Skip 10, return 10

Pagination

Implement pagination using LIMIT and OFFSET together.

-- Page 1 (rows 1-10)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

Pagination Formula

OFFSET = (page_number - 1) × page_size

⚠️ OFFSET-based pagination becomes slow with large offsets. For million-row tables, use cursor-based pagination instead.

Cursor-Based Pagination

-- Faster for large datasets: use last seen ID
SELECT * FROM products
WHERE product_id > 1000  -- Last seen ID
ORDER BY product_id
LIMIT 10;

FETCH (ANSI Standard)

-- SQL Server / PostgreSQL
SELECT * FROM employees
ORDER BY employee_id
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

TOP (SQL Server)

-- SQL Server specific
SELECT TOP 10 * FROM employees
ORDER BY salary DESC;

RANDOM Ordering

-- MySQL
SELECT * FROM products
ORDER BY RAND()
LIMIT 5;

-- PostgreSQL
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 5;

⚠️ ORDER BY RAND() is extremely slow on large tables. For random sampling, consider alternative approaches like using a random ID range.

✏️ Exercise: Write a query to get the 3rd highest salary from the employees table, and another query to paginate through orders showing 20 per page (page 5).

See Solution

-- 3rd highest salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- Page 5 of orders (20 per page)
SELECT *
FROM orders
ORDER BY order_id
LIMIT 20 OFFSET 80;

✅ Key Takeaways

1. ORDER BY sorts results — ASC is default, DESC for reverse order
2. LIMIT restricts how many rows are returned
3. OFFSET skips rows before returning results
4. Pagination uses LIMIT + OFFSET: OFFSET = (page - 1) × size
5. For large datasets, prefer cursor-based pagination over OFFSET