SQL - ORDER BY Clause (ASC, DESC)
The ORDER BY clause appears at the end of a SELECT statement and determines the sequence in which rows are returned. The fundamental syntax follows this pattern:
Key Insights
- The ORDER BY clause sorts query results by one or more columns in ascending (ASC) or descending (DESC) order, with ASC as the default when no direction is specified.
- Multiple column sorting applies hierarchical ordering where subsequent columns act as tiebreakers when previous columns contain duplicate values.
- Performance optimization requires understanding index usage, as sorting large datasets without proper indexes forces expensive filesort operations that can severely impact query execution time.
Basic Syntax and Single Column Sorting
The ORDER BY clause appears at the end of a SELECT statement and determines the sequence in which rows are returned. The fundamental syntax follows this pattern:
SELECT column1, column2, column3
FROM table_name
ORDER BY column1 [ASC|DESC];
Consider a products table with pricing information:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2),
stock_quantity INT
);
INSERT INTO products VALUES
(1, 'Laptop Pro', 'Electronics', 1299.99, 45),
(2, 'Wireless Mouse', 'Electronics', 29.99, 150),
(3, 'Office Chair', 'Furniture', 249.99, 30),
(4, 'Standing Desk', 'Furniture', 599.99, 12),
(5, 'USB-C Cable', 'Electronics', 19.99, 200);
Sorting by price in ascending order (cheapest first):
SELECT product_name, price
FROM products
ORDER BY price ASC;
Results:
USB-C Cable | 19.99
Wireless Mouse | 29.99
Office Chair | 249.99
Standing Desk | 599.99
Laptop Pro | 1299.99
Descending order returns the most expensive items first:
SELECT product_name, price
FROM products
ORDER BY price DESC;
Multiple Column Sorting
When sorting by multiple columns, the database applies a hierarchical ordering strategy. The first column determines primary sorting, and subsequent columns resolve ties.
SELECT category, product_name, price
FROM products
ORDER BY category ASC, price DESC;
This query groups products by category alphabetically, then within each category, sorts by price from highest to lowest:
Electronics | Laptop Pro | 1299.99
Electronics | Wireless Mouse | 29.99
Electronics | USB-C Cable | 19.99
Furniture | Standing Desk | 599.99
Furniture | Office Chair | 249.99
The sorting direction can differ for each column:
SELECT category, stock_quantity, product_name
FROM products
ORDER BY category ASC, stock_quantity DESC, product_name ASC;
This sorts categories alphabetically, then by stock quantity (highest first) within each category, and finally alphabetically by product name when stock quantities match.
Sorting with Expressions and Calculations
ORDER BY accepts expressions, not just column names. This enables sorting based on calculated values:
SELECT
product_name,
price,
stock_quantity,
(price * stock_quantity) AS inventory_value
FROM products
ORDER BY (price * stock_quantity) DESC;
Results show products sorted by total inventory value:
Laptop Pro | 1299.99 | 45 | 58499.55
Wireless Mouse | 29.99 | 150 | 4498.50
Office Chair | 249.99 | 30 | 7499.70
Standing Desk | 599.99 | 12 | 7199.88
USB-C Cable | 19.99 | 200 | 3998.00
You can also use column aliases defined in the SELECT clause:
SELECT
product_name,
ROUND(price * 0.8, 2) AS discounted_price
FROM products
ORDER BY discounted_price DESC;
Handling NULL Values
NULL values require special consideration in sorting. Different database systems handle NULLs differently, but most treat them as either the lowest or highest values.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
commission DECIMAL(10,2)
);
INSERT INTO employees VALUES
(1, 'Alice Johnson', 'Sales', 5000.00),
(2, 'Bob Smith', 'Engineering', NULL),
(3, 'Carol White', 'Sales', 7500.00),
(4, 'David Brown', 'Marketing', NULL),
(5, 'Eve Davis', 'Sales', 3000.00);
In most SQL databases (MySQL, PostgreSQL, SQL Server), NULLs sort first in ASC and last in DESC:
SELECT name, commission
FROM employees
ORDER BY commission ASC;
PostgreSQL and Oracle allow explicit NULL positioning:
-- PostgreSQL syntax
SELECT name, commission
FROM employees
ORDER BY commission ASC NULLS LAST;
SELECT name, commission
FROM employees
ORDER BY commission DESC NULLS FIRST;
Sorting with CASE Expressions
CASE expressions enable custom sorting logic that doesn’t follow standard alphabetical or numerical order:
SELECT product_name, category, stock_quantity
FROM products
ORDER BY
CASE category
WHEN 'Electronics' THEN 1
WHEN 'Furniture' THEN 2
ELSE 3
END,
stock_quantity DESC;
This prioritizes Electronics over Furniture, then sorts by stock quantity within each category.
Another practical example uses CASE to implement business-specific sorting:
SELECT product_name, stock_quantity
FROM products
ORDER BY
CASE
WHEN stock_quantity < 20 THEN 1 -- Critical stock
WHEN stock_quantity < 50 THEN 2 -- Low stock
ELSE 3 -- Normal stock
END,
product_name;
Performance Considerations and Index Usage
Sorting large result sets without indexes triggers filesort operations that consume significant memory and CPU resources. The EXPLAIN command reveals whether your query uses indexes:
EXPLAIN SELECT product_name, price
FROM products
ORDER BY price DESC;
Creating an index on sorted columns dramatically improves performance:
CREATE INDEX idx_products_price ON products(price);
For multi-column sorting, composite indexes should match the ORDER BY column sequence:
CREATE INDEX idx_category_price ON products(category, price);
-- This query efficiently uses the index
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;
However, this index won’t help if you only sort by price, as it’s not the leading column in the index.
Limiting Sorted Results
Combining ORDER BY with LIMIT (or TOP in SQL Server) retrieves only the top N records:
-- MySQL, PostgreSQL
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3;
-- SQL Server
SELECT TOP 3 product_name, price
FROM products
ORDER BY price DESC;
This pattern is essential for pagination:
-- Page 1 (items 1-10)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;
-- Page 2 (items 11-20)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;
Common Pitfalls
Sorting by position instead of column name works but reduces readability:
-- Avoid this
SELECT product_name, price, category
FROM products
ORDER BY 2 DESC;
-- Prefer explicit column names
SELECT product_name, price, category
FROM products
ORDER BY price DESC;
Sorting after UNION requires placing ORDER BY after the final SELECT:
SELECT product_name, price FROM products WHERE category = 'Electronics'
UNION
SELECT product_name, price FROM products WHERE category = 'Furniture'
ORDER BY price DESC; -- Applies to the entire result set
Character set and collation affect string sorting. Ensure consistent collation settings:
SELECT product_name
FROM products
ORDER BY product_name COLLATE utf8mb4_unicode_ci;
The ORDER BY clause is fundamental to SQL query construction, transforming unordered result sets into meaningful, organized data. Proper indexing strategy combined with understanding sorting behavior ensures both accurate results and optimal performance.