#MySQL ORDER BY

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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