-- Ascending order (default)
SELECT first_name, last_name, salary
FROM employees
ORDER BY last_name ASC;

-- Descending order
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- ASC is the default, so these are equivalent:
-- ORDER BY last_name
-- ORDER BY last_name ASC

-- Sort by department ASC, then salary DESC within each department
SELECT first_name, last_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

-- Three-level sort: region, then country, then city
SELECT city, country, region, population
FROM cities
ORDER BY region ASC, country ASC, city ASC;

-- Mix directions freely
SELECT product_name, category, price, stock
FROM products
ORDER BY category ASC, price DESC, stock ASC;

-- 1 = first_name, 2 = last_name, 3 = salary
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 2 ASC;

-- Sort by a calculated value
SELECT product_name, price, quantity,
       price * quantity AS line_total
FROM order_items
ORDER BY price * quantity DESC;

-- MySQL allows aliases in ORDER BY (evaluated after SELECT)
SELECT product_name, price * quantity AS line_total
FROM order_items
ORDER BY line_total DESC;

-- Sort by string length
SELECT username, LENGTH(username) AS len
FROM users
ORDER BY len DESC;

-- Sort by day of week (1=Sunday in MySQL)
SELECT event_name, event_date
FROM events
ORDER BY DAYOFWEEK(event_date), event_date;

-- Default: NULLs first in ASC order
SELECT name, manager_id FROM employees ORDER BY manager_id ASC;
-- manager_id: NULL, NULL, 1, 2, 3

-- Force NULLs LAST in ASC order
SELECT name, manager_id
FROM employees
ORDER BY (manager_id IS NULL) ASC, manager_id ASC;
-- (manager_id IS NULL) returns 0 for non-null, 1 for null
-- So 0s (non-null) sort first, 1s (null) sort last
-- Result: 1, 2, 3, NULL, NULL

-- Force NULLs FIRST in DESC order (they would normally be last)
SELECT name, manager_id
FROM employees
ORDER BY (manager_id IS NULL) DESC, manager_id DESC;
-- Result: NULL, NULL, 3, 2, 1

-- Force NULLs LAST in DESC order (the default already does this)
SELECT name, manager_id
FROM employees
ORDER BY manager_id DESC;
-- Result: 3, 2, 1, NULL, NULL (NULLs naturally last in DESC)

-- Sort orders by a business-defined status priority
SELECT id, status, total, created_at
FROM orders
ORDER BY FIELD(status, 'urgent', 'pending', 'processing', 'shipped', 'delivered', 'cancelled');
-- 'urgent' = 1, 'pending' = 2, ..., items not in list = 0 (sort first)

-- Push unlisted statuses to the end with a secondary sort
SELECT id, status, total
FROM orders
ORDER BY
  FIELD(status, 'urgent', 'pending', 'processing', 'shipped', 'delivered', 'cancelled') = 0 ASC,
  FIELD(status, 'urgent', 'pending', 'processing', 'shipped', 'delivered', 'cancelled') ASC;

-- Custom day-of-week sort starting Monday
SELECT event_name, event_day
FROM events
ORDER BY FIELD(event_day, 'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');

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

-- Most recent 10 orders
SELECT id, customer_id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

-- Page 3 of results (10 per page): skip 20, take 10
SELECT id, title
FROM articles
ORDER BY published_at DESC
LIMIT 10 OFFSET 20;

-- Leaderboard: top 100 players with ties handled by secondary sort
SELECT player_name, score, games_played
FROM leaderboard
ORDER BY score DESC, games_played ASC
LIMIT 100;

-- Without index: sorts all 5M rows, then returns top 10 (slow)
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Extra: Using filesort  <-- sorts everything

-- With index: reads 10 entries from index in reverse, done (fast)
ALTER TABLE orders ADD INDEX idx_created_at (created_at);
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- type: index, Extra: NULL  <-- walks index backward

-- Check sort buffer size
SHOW VARIABLES LIKE 'sort_buffer_size';       -- default: 256K
SHOW VARIABLES LIKE 'max_length_for_sort_data'; -- default: 4096 bytes

-- Increase sort buffer for large sorts (session level)
SET SESSION sort_buffer_size = 4 * 1024 * 1024;  -- 4 MB

-- Check if a query is spilling to disk
SHOW STATUS LIKE 'Sort_merge_passes';  -- > 0 means disk spill occurred

-- Covering index: status (equality) + created_at (range/sort) + id + total (SELECT)
ALTER TABLE orders
  ADD INDEX idx_cover (status, created_at, id, total);

SELECT id, total
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

-- Good: equality on status, then ORDER BY created_at
-- Index: (status, created_at) — MySQL walks the index in order
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;

-- Bad: range on created_at breaks ORDER BY index usage
-- Index: (status, created_at) can't be used for ORDER BY here
SELECT id FROM orders
WHERE status = 'pending'
  AND created_at > '2024-01-01'
ORDER BY total DESC;  -- total is not in the index after a range

-- ORDER BY in derived table without LIMIT is ignored in MySQL 8.0+
SELECT * FROM (
  SELECT id, name FROM products ORDER BY name  -- this ORDER BY is ignored
) AS sub
ORDER BY name;  -- this ORDER BY is what actually sorts

-- If you need ORDER BY inside a subquery, add LIMIT
SELECT * FROM (
  SELECT id, name FROM products ORDER BY name LIMIT 999999999
) AS sub;  -- ORDER BY now honored (with LIMIT present)

-- Fully deterministic: department sort, salary tiebreaker, PK final tiebreaker
SELECT id, first_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC, id ASC;
-- id is the PK -- guarantees same row order on every run

-- Leaderboard (score desc, earlier join date wins ties)
SELECT username, score, joined_at
FROM players
ORDER BY score DESC, joined_at ASC
LIMIT 50;

-- Social feed (promoted posts first, then newest)
SELECT id, title, promoted, published_at
FROM posts
ORDER BY promoted DESC, published_at DESC
LIMIT 20;

-- Invoice aging report (oldest unpaid first)
SELECT invoice_id, customer, due_date, amount
FROM invoices
WHERE paid = 0
ORDER BY due_date ASC;

-- Admin user list (locked accounts last, then alpha)
SELECT username, account_locked, created_at
FROM users
ORDER BY account_locked ASC, username ASC;