MySQL ORDER BY Clause

The ORDER BY clause is used to sort the result set in either ascending or descending order. By default, it sorts in ascending order. You can sort by one or more columns.

Basic Syntax

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

Ascending Order (ASC)

ASC is the default and sorts from smallest to largest:

-- Sort by salary (lowest to highest)
SELECT name, salary 
FROM employees 
ORDER BY salary ASC;

-- ASC is optional (same result)
SELECT name, salary 
FROM employees 
ORDER BY salary;

Descending Order (DESC)

DESC sorts from largest to smallest:

-- Sort by salary (highest to lowest)
SELECT name, salary 
FROM employees 
ORDER BY salary DESC;

Sorting by Multiple Columns

You can sort by multiple columns with different sort directions:

-- Sort by department (A-Z), then by salary (high to low)
SELECT department, name, salary
FROM employees
ORDER BY department ASC, salary DESC;

-- Complex multi-column sorting
SELECT 
    last_name,
    first_name,
    department,
    hire_date,
    salary
FROM employees
ORDER BY 
    department ASC,
    last_name ASC,
    first_name ASC,
    hire_date DESC;

Sorting by Column Position

You can sort by column position instead of name:

-- Sort by 3rd column (salary), then 2nd column (name)
SELECT name, email, salary
FROM employees
ORDER BY 3 DESC, 2 ASC;

Sorting with Expressions

You can sort by calculated columns or expressions:

-- Sort by full name length
SELECT name, LENGTH(name) as name_length
FROM employees
ORDER BY LENGTH(name) DESC;

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

CASE Statement in ORDER BY

Use CASE for custom sorting logic:

-- Custom priority sorting
SELECT 
    task_name,
    priority,
    due_date
FROM tasks
ORDER BY 
    CASE priority
        WHEN 'High' THEN 1
        WHEN 'Medium' THEN 2
        WHEN 'Low' THEN 3
        ELSE 4
    END,
    due_date ASC;

-- Sort department in custom order
SELECT 
    department,
    name,
    salary
FROM employees
ORDER BY 
    CASE department
        WHEN 'Executive' THEN 1
        WHEN 'Management' THEN 2
        WHEN 'Sales' THEN 3
        WHEN 'Engineering' THEN 4
        ELSE 5
    END,
    salary DESC;

NULL Values in Sorting

By default, NULL values come first in ASC order and last in DESC order:

-- NULLS will appear first
SELECT name, commission
FROM employees
ORDER BY commission ASC;

-- NULLS will appear last
SELECT name, commission
FROM employees
ORDER BY commission DESC;

Practical Examples

-- Comprehensive employee sorting
SELECT 
    employee_id,
    CONCAT(last_name, ', ', first_name) AS full_name,
    department,
    job_title,
    salary,
    hire_date,
    DATEDIFF(CURDATE(), hire_date) / 365 AS years_of_service
FROM employees
WHERE active = 1
ORDER BY 
    department ASC,
    job_title ASC,
    years_of_service DESC,
    last_name ASC,
    first_name ASC;

-- E-commerce product sorting options
SELECT 
    product_id,
    product_name,
    category,
    brand,
    price,
    discount_percent,
    price * (1 - discount_percent/100) AS final_price,
    rating,
    review_count,
    stock_quantity,
    CASE 
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM products
WHERE active = 1
ORDER BY 
    -- Default: featured products first, then by popularity
    featured DESC,
    rating DESC,
    review_count DESC,
    final_price ASC;

-- Sales performance report
SELECT 
    s.salesperson_id,
    CONCAT(e.first_name, ' ', e.last_name) AS salesperson_name,
    e.department,
    COUNT(DISTINCT s.order_id) AS total_orders,
    SUM(s.amount) AS total_sales,
    AVG(s.amount) AS avg_order_value,
    MAX(s.order_date) AS last_sale_date
FROM sales s
JOIN employees e ON s.salesperson_id = e.employee_id
WHERE s.order_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND s.status = 'Completed'
GROUP BY s.salesperson_id, e.first_name, e.last_name, e.department
ORDER BY 
    e.department ASC,
    total_sales DESC,
    total_orders DESC,
    last_sale_date DESC;

Performance Considerations

• Add indexes on columns frequently used in ORDER BY clauses
• Avoid sorting on unindexed columns for large datasets
• Use LIMIT with ORDER BY to reduce sorting overhead
• Consider denormalization for frequently sorted computed columns

Common Sorting Patterns