Mastering ORDER BY - Sorting Results

Duration: 75 minutes

Module 3: Session 5

Learning Objectives

Master single and multi-column sorting
Implement custom sort orders and collations
Handle NULL values in sorting
Optimize ORDER BY performance with indexes
Use advanced sorting techniques and expressions
Understand sorting with GROUP BY and aggregates
Apply real-world sorting patterns

ORDER BY Fundamentals

ORDER BY determines the sequence of rows in your result set. Master it to present data meaningfully! 📊

💡

Key ORDER BY Facts

Default sort order is ASC (ascending)
NULL values sort first in ASC, last in DESC (MySQL default)
Sorting happens after WHERE, GROUP BY, and HAVING
Can sort by columns not in SELECT list
Multiple columns create hierarchical sorting

Basic and Multi-Column Sorting

graph TB ORDER[ORDER BY] --> SINGLE[Single Column] ORDER --> MULTI[Multiple Columns] ORDER --> EXPR[Expressions] ORDER --> POS[Position] SINGLE --> ASC[ASC/Ascending] SINGLE --> DESC[DESC/Descending] MULTI --> HIER[Hierarchical Sort] MULTI --> MIX[Mixed Directions] EXPR --> CALC[Calculations] EXPR --> FUNC[Functions] EXPR --> CASE[CASE Statements] style ORDER fill:#dcfce7 style ASC fill:#dbeafe style DESC fill:#fee2e2 style HIER fill:#fef3c7

Comprehensive ORDER BY Examples

-- ========================================
-- BASIC SORTING
-- ========================================

-- Simple ascending (default)
SELECT * FROM products 
ORDER BY price;

-- Explicit ascending
SELECT * FROM products 
ORDER BY price ASC;

-- Descending order
SELECT * FROM products 
ORDER BY price DESC;

-- Sort by column not in SELECT
SELECT product_name, price 
FROM products 
ORDER BY stock_quantity DESC;  -- Valid!

-- ========================================
-- MULTI-COLUMN SORTING
-- ========================================

-- Hierarchical sorting
SELECT * FROM users 
ORDER BY 
    country,      -- First by country
    state,        -- Then by state within country
    city,         -- Then by city within state
    last_name,    -- Then by last name
    first_name;   -- Finally by first name

-- Mixed directions
SELECT * FROM products 
ORDER BY 
    category ASC,       -- Categories A-Z
    price DESC,         -- Expensive first within category
    product_name ASC;   -- Alphabetical for same price

-- Real-world example: E-commerce listing
SELECT 
    product_id,
    product_name,
    category,
    price,
    rating,
    review_count
FROM products 
WHERE status = 'active'
ORDER BY 
    is_featured DESC,   -- Featured products first
    rating DESC,        -- Highest rated next
    review_count DESC,  -- Most reviewed next
    price ASC;          -- Cheapest last

-- ========================================
-- SORTING BY POSITION
-- ========================================

-- Sort by column position (not recommended)
SELECT username, email, created_at 
FROM users 
ORDER BY 3 DESC;  -- Sort by 3rd column (created_at)

-- Multiple positions
SELECT category, product_name, price 
FROM products 
ORDER BY 1, 3 DESC;  -- By category, then price DESC

-- ========================================
-- SORTING WITH EXPRESSIONS
-- ========================================

-- Sort by calculated value
SELECT 
    product_name,
    price,
    discount_percent,
    price * (1 - discount_percent/100) AS final_price
FROM products 
ORDER BY final_price;

-- Sort by expression not in SELECT
SELECT product_name, price 
FROM products 
ORDER BY (price * stock_quantity) DESC;  -- By inventory value

-- Sort by string manipulation
SELECT * FROM users 
ORDER BY 
    LENGTH(username),           -- Shortest usernames first
    SUBSTRING(email, 1, 3);     -- Then by first 3 chars of email

-- Sort by date parts
SELECT * FROM orders 
ORDER BY 
    YEAR(order_date) DESC,
    MONTH(order_date),
    DAY(order_date);

-- ========================================
-- CONDITIONAL SORTING
-- ========================================

-- CASE in ORDER BY
SELECT * FROM orders 
ORDER BY 
    CASE status
        WHEN 'urgent' THEN 1
        WHEN 'high' THEN 2
        WHEN 'normal' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
    END,
    created_at ASC;

-- Different sort based on condition
SELECT * FROM products 
ORDER BY 
    CASE 
        WHEN category = 'Electronics' THEN price DESC
        WHEN category = 'Books' THEN title ASC
        ELSE created_at DESC
    END;

-- Sort by boolean conditions
SELECT * FROM users 
ORDER BY 
    (subscription_type = 'premium') DESC,  -- Premium users first
    (last_login > DATE_SUB(NOW(), INTERVAL 7 DAY)) DESC,  -- Active users
    registration_date ASC;  -- Oldest users

-- ========================================
-- COLLATION AND CASE SENSITIVITY
-- ========================================

-- Case-insensitive sort (default)
SELECT * FROM users 
ORDER BY username;  -- 'Apple' and 'apple' treated same

-- Case-sensitive sort using BINARY
SELECT * FROM users 
ORDER BY BINARY username;  -- 'Apple' before 'apple'

-- Specific collation
SELECT * FROM products 
ORDER BY product_name COLLATE utf8mb4_unicode_ci;

-- Language-specific sorting
SELECT * FROM customers 
ORDER BY last_name COLLATE utf8mb4_german2_ci;  -- German sort rules

NULL Handling in ORDER BY

Advanced NULL Handling

-- ========================================
-- NULL POSITIONING CONTROL
-- ========================================

-- Default MySQL behavior
SELECT * FROM products ORDER BY discount ASC;   -- NULLs first
SELECT * FROM products ORDER BY discount DESC;  -- NULLs last

-- Force NULLs last in ascending order
SELECT * FROM products 
ORDER BY 
    discount IS NULL,  -- FALSE (0) for non-NULL, TRUE (1) for NULL
    discount ASC;

-- Force NULLs first in descending order
SELECT * FROM products 
ORDER BY 
    discount IS NOT NULL,  -- FALSE (0) for NULL, TRUE (1) for non-NULL
    discount DESC;

-- Treat NULL as specific value
SELECT * FROM products 
ORDER BY COALESCE(discount, 0) DESC;  -- NULL treated as 0

SELECT * FROM products 
ORDER BY IFNULL(discount, 999) ASC;  -- NULL treated as 999

-- ========================================
-- COMPLEX NULL HANDLING
-- ========================================

-- Multi-column with different NULL handling
SELECT 
    product_name,
    category,
    discount,
    rating
FROM products 
ORDER BY 
    category ASC,                    -- Normal sort
    discount IS NULL,                 -- Non-NULL discounts first
    discount DESC,                    -- Highest discount first
    COALESCE(rating, 0) DESC;        -- NULL ratings as 0

-- Conditional NULL handling
SELECT * FROM users 
ORDER BY 
    CASE 
        WHEN last_login IS NULL THEN '1900-01-01'  -- Never logged in
        ELSE last_login 
    END DESC;

-- NULL priority in business logic
SELECT 
    task_id,
    task_name,
    due_date,
    priority
FROM tasks 
ORDER BY 
    -- Overdue tasks first
    (due_date < NOW() AND status != 'completed') DESC,
    -- Then by priority (NULL = lowest)
    CASE 
        WHEN priority IS NULL THEN 999
        ELSE priority 
    END ASC,
    -- Then by due date (NULL = no deadline = last)
    due_date IS NULL,
    due_date ASC;

-- ========================================
-- NULL IN EXPRESSIONS
-- ========================================

-- Sorting by expression with NULL handling
SELECT 
    product_name,
    price,
    discount,
    price * (1 - COALESCE(discount, 0)/100) AS final_price
FROM products 
ORDER BY 
    final_price ASC;

-- NULL-safe comparison in ORDER BY
SELECT * FROM products 
ORDER BY 
    price <=> 100;  -- NULL-safe equals operator

Custom Ordering Techniques

Custom Sort Orders

-- ========================================
-- FIELD() FUNCTION FOR CUSTOM ORDER
-- ========================================

-- Specific value order
SELECT * FROM orders 
ORDER BY FIELD(status, 
    'urgent',
    'pending',
    'processing',
    'shipped',
    'delivered',
    'cancelled'
);

-- Day of week custom order (Monday first)
SELECT * FROM schedules 
ORDER BY FIELD(day_name,
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
    'Saturday',
    'Sunday'
);

-- Size ordering (not alphabetical)
SELECT * FROM products 
WHERE category = 'Clothing'
ORDER BY FIELD(size, 'XS', 'S', 'M', 'L', 'XL', 'XXL');

-- ========================================
-- ENUM NATURAL ORDERING
-- ========================================

-- If using ENUM type, it sorts by index
CREATE TABLE products_enum (
    size ENUM('XS', 'S', 'M', 'L', 'XL', 'XXL')
);

-- Sorts by ENUM index (1=XS, 2=S, etc.)
SELECT * FROM products_enum ORDER BY size;

-- ========================================
-- WEIGHTED SORTING
-- ========================================

-- Sort by relevance score
SELECT 
    product_id,
    product_name,
    (
        (name LIKE '%laptop%') * 10 +
        (description LIKE '%laptop%') * 5 +
        (category = 'Computers') * 3
    ) AS relevance_score
FROM products 
WHERE 
    name LIKE '%laptop%' 
    OR description LIKE '%laptop%'
    OR category = 'Computers'
ORDER BY relevance_score DESC;

-- Multi-factor sorting algorithm
SELECT 
    product_id,
    product_name,
    price,
    rating,
    review_count,
    -- Calculate sort score
    (
        (rating / 5) * 40 +                           -- 40% weight to rating
        (LEAST(review_count, 100) / 100) * 30 +       -- 30% to reviews (capped)
        ((1000 - LEAST(price, 1000)) / 1000) * 20 +   -- 20% to price (inverse)
        (is_featured * 10)                            -- 10% bonus if featured
    ) AS sort_score
FROM products 
WHERE category = 'Electronics'
ORDER BY sort_score DESC;

-- ========================================
-- DYNAMIC SORTING (Application Logic)
-- ========================================

-- Prepare for dynamic sort column
SET @sort_column = 'price';
SET @sort_direction = 'DESC';

-- Using CASE for dynamic sorting
SELECT * FROM products 
ORDER BY 
    CASE @sort_column
        WHEN 'name' THEN product_name
        WHEN 'price' THEN CAST(price AS CHAR)
        WHEN 'date' THEN CAST(created_at AS CHAR)
        ELSE product_id
    END,
    CASE @sort_direction
        WHEN 'DESC' THEN -1
        ELSE 1
    END;

-- ========================================
-- RANDOM ORDERING
-- ========================================

-- Random order (inefficient for large tables)
SELECT * FROM products 
ORDER BY RAND() 
LIMIT 10;

-- Better random selection for large tables
SELECT * FROM products 
WHERE RAND() < (10 / (SELECT COUNT(*) FROM products))
ORDER BY RAND()
LIMIT 10;

-- Reproducible random order (with seed)
SELECT * FROM products 
ORDER BY RAND(12345)  -- Same order every time with same seed
LIMIT 10;

-- ========================================
-- NATURAL SORTING (Alphanumeric)
-- ========================================

-- Problem: '10' sorts before '2' in strings
-- Solution: Natural sort

-- Extract numbers for natural sorting
SELECT 
    product_code
FROM products 
ORDER BY 
    CAST(product_code AS UNSIGNED),  -- Numeric part
    product_code;                     -- Then alphabetic

-- More complex natural sort
SELECT 
    filename
FROM files 
ORDER BY 
    LENGTH(filename),
    filename;

-- Using REGEXP for natural sort
SELECT 
    item_code
FROM inventory
ORDER BY 
    REGEXP_REPLACE(item_code, '[^0-9]', ''),  -- Numbers only
    REGEXP_REPLACE(item_code, '[0-9]', '');   -- Letters only

Performance Optimization

ORDER BY Performance Optimization

-- ========================================
-- INDEX OPTIMIZATION FOR ORDER BY
-- ========================================

-- Create index for single column sort
CREATE INDEX idx_price ON products(price);
SELECT * FROM products ORDER BY price;  -- Uses index

-- Composite index for multi-column sort
CREATE INDEX idx_category_price ON products(category, price);
SELECT * FROM products 
ORDER BY category, price;  -- Uses index

-- Index for WHERE + ORDER BY
CREATE INDEX idx_status_date ON orders(status, order_date);
SELECT * FROM orders 
WHERE status = 'pending'
ORDER BY order_date DESC;  -- Uses index

-- Covering index (includes all needed columns)
CREATE INDEX idx_covering 
ON products(category, price, product_name, product_id);

SELECT product_id, product_name, price 
FROM products 
WHERE category = 'Electronics'
ORDER BY price;  -- All data from index!

-- ========================================
-- EXPLAIN ANALYSIS
-- ========================================

-- Check if filesort is used
EXPLAIN SELECT * FROM products ORDER BY price;
-- Look for "Using filesort" in Extra column

-- Analyze complex query
EXPLAIN FORMAT=JSON
SELECT 
    p.product_name,
    c.category_name,
    p.price
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'active'
ORDER BY c.category_name, p.price DESC;

-- ========================================
-- OPTIMIZING COMPLEX SORTS
-- ========================================

-- Bad: Function in ORDER BY prevents index use
SELECT * FROM orders 
ORDER BY YEAR(order_date), MONTH(order_date);

-- Good: Use columns directly
SELECT * FROM orders 
ORDER BY order_date;

-- Bad: Sorting large result set
SELECT * FROM huge_table 
ORDER BY some_column;

-- Good: Limit before sorting
SELECT * FROM (
    SELECT * FROM huge_table 
    WHERE conditions 
    LIMIT 10000
) AS subset 
ORDER BY some_column;

-- ========================================
-- PAGINATION OPTIMIZATION
-- ========================================

-- Inefficient for large offsets
SELECT * FROM products 
ORDER BY product_id 
LIMIT 10000, 10;  -- Scans 10010 rows!

-- Better: Seek method
SELECT * FROM products 
WHERE product_id > 10000  -- Last ID from previous page
ORDER BY product_id 
LIMIT 10;

-- Best: Keyset pagination
SELECT * FROM products 
WHERE (category, product_id) > ('Electronics', 1234)
ORDER BY category, product_id 
LIMIT 10;

-- ========================================
-- AVOIDING FILESORT
-- ========================================

-- Force index usage
SELECT * FROM products 
FORCE INDEX (idx_price)
ORDER BY price;

-- Check sort buffer size
SHOW VARIABLES LIKE 'sort_buffer_size';

-- Increase for large sorts (session level)
SET SESSION sort_buffer_size = 8388608;  -- 8MB

-- Monitor sort performance
SHOW STATUS LIKE 'Sort%';

-- ========================================
-- DENORMALIZATION FOR SORTING
-- ========================================

-- Add computed column for complex sorts
ALTER TABLE products 
ADD COLUMN sort_score INT GENERATED ALWAYS AS (
    (rating * 100) + review_count
) STORED,
ADD INDEX idx_sort_score (sort_score);

-- Now sorting is fast
SELECT * FROM products 
ORDER BY sort_score DESC;

Practice Exercise

💻

Advanced Sorting Challenges

Write ORDER BY queries for these scenarios:

Sort products by popularity (rating × reviews)
List users by last name, but "Smith" always first
Sort orders by custom priority: urgent→today→tomorrow→rest
Natural sort for item codes (ITEM1, ITEM10, ITEM2)
Sort with NULLs in middle (not first or last)
Random featured product, then by price
Sort by distance from a point (geospatial)
Pagination with consistent ordering

💡 Click for Solutions

-- 1. Sort by popularity (rating × reviews)
SELECT 
    product_id,
    product_name,
    rating,
    review_count,
    (rating * review_count) AS popularity_score
FROM products 
WHERE status = 'active'
ORDER BY 
    popularity_score DESC,
    rating DESC,  -- Tie breaker
    product_name ASC;

-- 2. "Smith" always first, then alphabetical
SELECT * FROM users 
ORDER BY 
    CASE 
        WHEN last_name = 'Smith' THEN 0
        ELSE 1
    END,
    last_name ASC,
    first_name ASC;

-- Alternative using FIELD()
SELECT * FROM users 
ORDER BY 
    FIELD(last_name, 'Smith') DESC,  -- Returns 1 for Smith, 0 for others
    last_name,
    first_name;

-- 3. Custom priority order
SELECT * FROM orders 
ORDER BY 
    CASE 
        WHEN priority = 'urgent' THEN 1
        WHEN DATE(due_date) = CURDATE() THEN 2  -- Today
        WHEN DATE(due_date) = DATE_ADD(CURDATE(), INTERVAL 1 DAY) THEN 3  -- Tomorrow
        ELSE 4  -- Everything else
    END,
    due_date ASC,
    created_at ASC;

-- 4. Natural sort for item codes
SELECT 
    item_code
FROM inventory
ORDER BY 
    -- Extract and sort by numeric part
    CAST(REGEXP_SUBSTR(item_code, '[0-9]+') AS UNSIGNED),
    -- Then by full code for items with same number
    item_code;

-- Alternative for MySQL < 8.0
SELECT 
    item_code
FROM inventory
ORDER BY 
    LENGTH(item_code),  -- Shorter codes first
    item_code;          -- Then alphabetically

-- 5. NULLs in middle
SELECT * FROM products 
ORDER BY 
    CASE 
        WHEN discount IS NULL THEN 1  -- Middle
        WHEN discount = 0 THEN 0      -- First
        ELSE 2                         -- Last
    END,
    discount DESC;

-- 6. Random featured, then by price
SELECT * FROM products 
WHERE category = 'Electronics'
ORDER BY 
    is_featured DESC,                           -- Featured first
    CASE WHEN is_featured = 1 THEN RAND() END,  -- Random if featured
    price ASC;                                   -- Price if not featured

-- 7. Sort by distance (geospatial)
-- Assuming you have latitude and longitude
SET @user_lat = 37.7749;
SET @user_lng = -122.4194;

SELECT 
    store_id,
    store_name,
    latitude,
    longitude,
    (
        6371 * acos(
            cos(radians(@user_lat)) * 
            cos(radians(latitude)) * 
            cos(radians(longitude) - radians(@user_lng)) + 
            sin(radians(@user_lat)) * 
            sin(radians(latitude))
        )
    ) AS distance_km
FROM stores
ORDER BY distance_km ASC
LIMIT 10;

-- Using ST_Distance for spatial data types
SELECT 
    store_id,
    store_name,
    ST_Distance_Sphere(
        location,
        ST_GeomFromText('POINT(-122.4194 37.7749)')
    ) / 1000 AS distance_km
FROM stores_spatial
ORDER BY distance_km
LIMIT 10;

-- 8. Pagination with consistent ordering
-- Page 1
SELECT * FROM products 
WHERE status = 'active'
ORDER BY created_at DESC, product_id DESC
LIMIT 20;

-- Page 2 (using last values from page 1)
SELECT * FROM products 
WHERE status = 'active'
  AND (created_at < '2024-01-15 10:30:00' 
       OR (created_at = '2024-01-15 10:30:00' AND product_id < 1234))
ORDER BY created_at DESC, product_id DESC
LIMIT 20;

PHP WordPress Development

Course Progress