MySQL ORDER BY

Overview

The ORDER BY clause sorts the result set by one or more columns. It can sort in ascending (ASC) or descending (DESC) order. By default, results are sorted in ascending order.

ORDER BY Syntax

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

Basic ORDER BY

Ascending Order

-- Default is ASC
SELECT * FROM users ORDER BY name;
SELECT * FROM users ORDER BY name ASC;

-- Sort by name alphabetically
SELECT * FROM products ORDER BY product_name;

Descending Order

-- Use DESC for descending
SELECT * FROM users ORDER BY created_at DESC;

-- Newest first
SELECT * FROM orders ORDER BY created_at DESC;

Multiple Column Sorting

-- Sort by multiple columns
SELECT * FROM users 
ORDER BY country ASC, name ASC;

-- Different directions
SELECT * FROM products 
ORDER BY category ASC, price DESC;

-- Three columns
SELECT * FROM orders 
ORDER BY 
    status ASC,
    created_at DESC,
    total DESC;

Sorting by Position

-- Sort by column position in SELECT
SELECT id, name, email FROM users ORDER BY 2;  -- Sort by name

-- Multiple positions
SELECT id, name, age FROM users ORDER BY 3 DESC, 2;

ORDER BY with Expressions

String Expressions

-- Sort by string length
SELECT * FROM users ORDER BY LENGTH(name);

-- Sort by part of string
SELECT * FROM emails ORDER BY SUBSTRING(email, LOCATE('@', email));

-- Case-insensitive sort
SELECT * FROM users ORDER BY name COLLATE utf8mb4_unicode_ci;

Numeric Expressions

-- Sort by calculation
SELECT * FROM products 
ORDER BY price * quantity DESC;

-- Sort by absolute value
SELECT * FROM transactions 
ORDER BY ABS(amount) DESC;

Date

-- Sort by date part
SELECT * FROM events ORDER BY YEAR(created_at) DESC;

-- Sort by time only
SELECT * FROM schedules ORDER BY TIME(scheduled_at);

-- Sort by relative date
SELECT * FROM users ORDER BY created_at DESC;

NULL Value Sorting

NULL Position

-- NULLs first (default ASC)
SELECT * FROM users ORDER BY phone ASC;  -- NULLs first

-- NULLs last
SELECT * FROM users ORDER BY phone DESC NULLS LAST;

-- In MySQL (NULLs are lowest by default)
SELECT * FROM users ORDER BY phone;          -- NULLs first
SELECT * FROM users ORDER BY phone DESC;     -- NULLs last

COALESCE for NULL Handling

-- Treat NULL as specific value
SELECT * FROM users 
ORDER BY COALESCE(phone, 'N/A');

-- Multiple NULL columns
SELECT * FROM users 
ORDER BY COALESCE(phone, mobile, email, 'No contact');

ORDER BY with LIMIT

Top N Results

-- Get top 10 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- Get newest 5 users
SELECT * FROM users ORDER BY created_at DESC LIMIT 5;

-- Get oldest 10 orders
SELECT * FROM orders ORDER BY created_at ASC LIMIT 10;

Pagination

-- Page 1 (records 1-10)
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 0;
SELECT * FROM products ORDER BY price DESC LIMIT 10;  -- Page 1

-- Page 2 (records 11-20)
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 10;

-- Page 3 (records 21-30)
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

Random Order

-- Random selection
SELECT * FROM users ORDER BY RAND() LIMIT 10;

-- Random product
SELECT * FROM products ORDER BY RAND() LIMIT 1;

-- Shuffle results
SELECT * FROM items ORDER BY RAND();

ORDER BY in Different Contexts

With JOIN

-- Sort joined results
SELECT u.name, o.order_number, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY o.created_at DESC;

-- Sort by aggregate
SELECT 
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
ORDER BY total_spent DESC;

With GROUP BY

-- Sort grouped results
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- Sort by aggregate
SELECT 
    user_id,
    COUNT(*) AS orders,
    AVG(total) AS avg_order
FROM orders
GROUP BY user_id
ORDER BY avg_order DESC;

With Subqueries

-- Sort subquery results
SELECT * FROM (
    SELECT * FROM products WHERE category = 'Electronics'
    ORDER BY price DESC
) AS electronics
LIMIT 10;

-- UNION with ORDER BY
(SELECT id, name FROM current_users ORDER BY created_at DESC LIMIT 10)
UNION
(SELECT id, name FROM archived_users ORDER BY created_at DESC LIMIT 10)
ORDER BY created_at DESC;

Collation and Sorting

Character Set

-- Different collation
SELECT * FROM users ORDER BY name COLLATE utf8mb4_unicode_ci;
SELECT * FROM users ORDER BY name COLLATE utf8mb4_bin;

Case-Insensitive Sort

-- Default is case-insensitive for utf8mb4
SELECT * FROM users ORDER BY name;  -- john, John, JOHN (case-insensitive)

-- Case-sensitive sort
SELECT * FROM users ORDER BY name COLLATE utf8mb4_bin;

Performance Considerations

Index Usage

-- Index helps for sorted columns
CREATE INDEX idx_users_name ON users(name);
SELECT * FROM users ORDER BY name;  -- Uses index

-- Composite index for multiple columns
CREATE INDEX idx_orders_status_date ON orders(status, created_at);
SELECT * FROM orders ORDER BY status, created_at;  -- Uses index

When Indexes Don't Help

-- Expression on column
SELECT * FROM users ORDER BY LENGTH(name);  -- Cannot use index

-- Function on column
SELECT * FROM users ORDER BY YEAR(created_at);  -- Cannot use index

-- Mixed directions
SELECT * FROM orders ORDER BY status ASC, created_at DESC;  -- May not use index

Practical Examples

E-commerce Sorting

-- Sort by price: low to high
SELECT * FROM products ORDER BY price ASC;

-- Sort by price: high to low
SELECT * FROM products ORDER BY price DESC;

-- Sort by rating
SELECT * FROM products ORDER BY rating DESC, reviews DESC;

-- Sort by newest
SELECT * FROM products ORDER BY created_at DESC;

User Management

-- Recently active users
SELECT * FROM users ORDER BY last_activity DESC;

-- Users by registration date
SELECT * FROM users ORDER BY created_at DESC;

-- Alphabetical by last name
SELECT * FROM users ORDER BY last_name, first_name;

Report Sorting

-- Sales by amount
SELECT 
    DATE(created_at) AS date,
    SUM(total) AS daily_sales
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY daily_sales DESC;

-- Top customers
SELECT 
    user_id,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM orders
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 10;

Summary

ORDER BY clause provides:

• Sorting: ASC (default), DESC
• Multiple Columns: Sort by multiple columns
• Expressions: Sort by computed values
• NULL Handling: NULLs first
• LIMIT: Top N results
• Performance: Use indexes for sorting

---

Previous: UNION

Next: GROUP BY