What is ORDER BY?

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

Ascending Order (Default)

-- ASC is optional (default behavior)
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary;

-- Explicit ASC (same result)
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary ASC;

Descending Order

-- DESC for highest to lowest
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

Sort by Multiple Columns

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

Sort by Column Position

-- Sort by the 3rd column in SELECT list
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC;

Sort with Expressions

-- Sort by computed value
SELECT name, price, stock
FROM products
ORDER BY price * stock DESC;

-- Sort by string length
SELECT first_name, last_name
FROM employees
ORDER BY LENGTH(last_name) DESC;

-- Sort by date difference
SELECT first_name, hire_date
FROM employees
ORDER BY DATEDIFF(CURRENT_DATE, hire_date) DESC;

Sort with Aliases

-- Use alias in ORDER BY
SELECT
    first_name,
    last_name,
    salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

Sort with NULL

-- PostgreSQL: Explicit NULL handling
SELECT first_name, email
FROM customers
ORDER BY email NULLS FIRST;

SELECT first_name, email
FROM customers
ORDER BY email NULLS LAST;

CASE in ORDER BY

-- Custom sort order
SELECT first_name, last_name, department
FROM employees
ORDER BY
    CASE department
        WHEN 'Executive' THEN 1
        WHEN 'Engineering' THEN 2
        WHEN 'Marketing' THEN 3
        ELSE 4
    END;

-- Sort by priority
SELECT task_name, priority
FROM tasks
ORDER BY
    CASE priority
        WHEN 'high' THEN 1
        WHEN 'medium' THEN 2
        WHEN 'low' THEN 3
    END;

ORDER BY with LIMIT

-- Top 5 highest salaries
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Bottom 3 products by price
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;

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

Performance Tips

-- Without index: Full sort needed
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- With index: Already sorted
CREATE INDEX idx_orders_date ON orders(order_date);
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10;

Common Mistakes

-- BAD: Using column position
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC;

-- GOOD: Using column name
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

-- BAD: ORDER BY without LIMIT for large tables
SELECT * FROM logs
ORDER BY created_at DESC;

-- GOOD: Add LIMIT
SELECT * FROM logs
ORDER BY created_at DESC
LIMIT 1000;

-- BAD: ORDER BY on multiple columns without clear need
SELECT * FROM products
ORDER BY name, price, stock, category, created_at;

-- GOOD: Only necessary columns
SELECT * FROM products
ORDER BY name;

Practice Exercises

Exercise 1: Sort employees by hire_date from newest to oldest.

SELECT * FROM employees
ORDER BY hire_date DESC;

Exercise 2: Sort products by price (lowest first), then by name alphabetically for same price.

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

Exercise 3: Find the 3 most recently hired employees.

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC
LIMIT 3;

Exercise 4: Sort customers by city alphabetically, then by last name within each city.

SELECT first_name, last_name, city
FROM customers
ORDER BY city ASC, last_name ASC;