MySQL ORDER BY Sort Results ASC DESC

ORDER BY Basics

Indexes can support ORDER BY only when their column order and query predicates align with the requested result order.

The ORDER BY clause sorts the result set by one or more columns. Use ASC for ascending order (default) and DESC for descending. Without ORDER BY, MySQL returns rows in an undefined order - never rely on the natural order of rows.

Single and Multiple Column Sorting

-- Sort by price ascending (cheapest first)
SELECT name, price, stock FROM products ORDER BY price ASC;

-- Sort by price descending (most expensive first)
SELECT name, price, stock FROM products ORDER BY price DESC;

-- Sort by multiple columns: first by category, then by price within each category
SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;

-- Sort by column alias
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    email
FROM customers
ORDER BY full_name ASC;

-- Sort by column position (1-based index) - less readable, avoid in production
SELECT name, price, stock FROM products ORDER BY 2 DESC;

ORDER BY with Expressions and FIELD()

ORDER BY with FIELD(), CASE, and NULL Ordering

-- FIELD(): sort by a custom order of values
-- Orders with status in a specific workflow order
SELECT order_id, status, ordered_at FROM orders
ORDER BY FIELD(status, 'pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- CASE in ORDER BY: custom sort logic
SELECT name, category, price FROM products
ORDER BY
    CASE category
        WHEN 'Electronics' THEN 1
        WHEN 'Office'       THEN 2
        WHEN 'Stationery'   THEN 3
        ELSE 4
    END,
    price ASC;

-- NULL ordering: NULLs sort first in ASC, last in DESC by default
-- Force NULLs to appear last in ASC order
SELECT customer_id, first_name, phone FROM customers
ORDER BY
    CASE WHEN phone IS NULL THEN 1 ELSE 0 END,
    phone ASC;

Top-N Queries with ORDER BY + LIMIT

Combining ORDER BY with LIMIT is the standard pattern for top-N queries - finding the most expensive products, most recent orders, highest-spending customers, etc.

Top-N Queries

-- Top 5 most expensive products
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;

-- 5 most recent orders
SELECT order_id, customer_id, total, ordered_at FROM orders
ORDER BY ordered_at DESC
LIMIT 5;

-- Cheapest product in each category (using subquery)
SELECT p.name, p.category, p.price
FROM products p
INNER JOIN (
    SELECT category, MIN(price) AS min_price
    FROM products
    GROUP BY category
) AS cheapest ON p.category = cheapest.category AND p.price = cheapest.min_price
ORDER BY p.category;

-- Sort by computed expression: inventory value
SELECT name, price, stock, (price * stock) AS inventory_value
FROM products
ORDER BY inventory_value DESC;

Sorting MySQL results predictably with ORDER BY

ORDER BY controls the sequence of returned rows. Without it, MySQL is free to return rows in whatever order the execution plan produces. That order may appear stable in a small table but change after indexes, inserts, deletes, or version upgrades.

Sorting can use one column or multiple columns. For example, a report may sort by department first, salary second, and name third. This creates stable, readable output. Sorting large result sets can be expensive, so indexes and LIMIT clauses matter for production lists and dashboards.

Use ASC for ascending and DESC for descending.
Add secondary sort columns when ties are possible.
Do not rely on insertion order unless it is explicitly sorted.
Consider indexes for frequently sorted columns.

Sort employees by department and salary

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC, name ASC;

Before you move on