MySQL ORDER BY

-- 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;

-- 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 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;