SQL Fundamentals
The ORDER BY Clause
Put your data in order β alphabetically, numerically, or chronologically.
- Ascending & Descending β control sort direction with ASC and DESC
- Multi-Column Sort β break ties with secondary sort columns
- Custom Ordering β use CASE for non-standard sort orders
ORDER BY transforms raw data into meaningful, organized results.
What is ORDER BY?
DfORDER BY
A SQL clause that sorts the result set by one or more columns. Results can be sorted in ascending (default) or descending order.
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;
| Component | Description | Example |
|---|---|---|
ORDER BY | Keyword | Required |
column1 | Column to sort by | salary |
ASC | Ascending order (default) | AβZ, 1β10 |
DESC | Descending order | ZβA, 10β1 |
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;
| first_name | last_name | salary |
|---|---|---|
| Dan | Wilson | 54000 |
| Bob | Smith | 62000 |
| Alice | Johnson | 75000 |
| Carol | Williams | 85000 |
| Eve | Brown | 91000 |
Descending Order
-- DESC for highest to lowest
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
| first_name | last_name | salary |
|---|---|---|
| Eve | Brown | 91000 |
| Carol | Williams | 85000 |
| Alice | Johnson | 75000 |
| Bob | Smith | 62000 |
| Dan | Wilson | 54000 |
Sort by Multiple Columns
DfMulti-Column Sort
When sorting by multiple columns, the first column determines the primary order. Rows with identical values in the first column are then sorted by the second column, and so on.
-- Sort by department, then salary within department
SELECT department, first_name, salary
FROM employees
ORDER BY department ASC, salary DESC;
| department | first_name | salary |
|---|---|---|
| Engineering | Eve | 91000 |
| Engineering | Carol | 85000 |
| Engineering | Alice | 75000 |
| Marketing | Bob | 62000 |
| Marketing | Dan | 54000 |
You can mix ASC and DESC in multi-column sorts. Each column has its own direction independent of the others.
Sort by Column Position
-- Sort by the 3rd column in SELECT list
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC;
Using column positions in ORDER BY makes code harder to maintain. If you change the SELECT list, the sort may break. Always use column names instead.
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;
| first_name | last_name | annual_salary |
|---|---|---|
| Eve | Brown | 1092000 |
| Carol | Williams | 1020000 |
| Alice | Johnson | 900000 |
Sort with NULL
DfNULL Sorting
NULL values sort differently depending on the database. In most systems, NULLs appear first in ASC order and last in DESC order. PostgreSQL provides explicit NULLS FIRST and NULLS LAST options.
-- 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;
| Database | Default Behavior |
|---|---|
| MySQL | NULLs sort first in ASC, last in DESC |
| PostgreSQL | Configurable with NULLS FIRST/LAST |
| SQL Server | NULLs sort first in ASC, last in DESC |
| Oracle | NULLs sort last in ASC, first in DESC |
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;
Use CASE in ORDER BY when you need a non-alphabetical, non-numeric sort order β like sorting by priority, status, or custom categories.
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
| Tip | Description | Impact |
|---|---|---|
| Add LIMIT | Sort fewer rows | High |
| Index sort column | Avoid filesort | High |
| Avoid SELECT * | Less data to sort | Medium |
| Avoid expressions | Can't use index | Medium |
| Use covering index | All data in index | High |
-- 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;
The ORDER BY clause is evaluated after SELECT, so you can reference column aliases defined in the SELECT list. However, avoid expressions in ORDER BY when possible β they prevent index usage.
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;
Key Takeaways
- ORDER BY sorts your results β ascending (ASC) by default
- Use DESC for descending order β highest to lowest, Z to A
- Sort by multiple columns for tie-breaking
- Always use column names, not positions β more maintainable
- NULLs sort differently by database β use NULLS FIRST/LAST for explicit control