Sorting With Order By

Why this matters

As a Business Analyst, you often need clean, prioritized views: top customers by revenue, most recent orders, oldest unresolved tickets, or product lists grouped by category. ORDER BY makes your output readable and decision-ready.

• Create ranked lists (e.g., highest spending customers this quarter)
• Prepare datasets for exports and stakeholder reviews
• Sort time-series to get latest-first or oldest-first trends
• Control tie-breakers so results are predictable and reproducible

Concept explained simply

ORDER BY tells the database how to sort the final result rows. If you don’t specify it, the order is not guaranteed and can change between runs.

• ASC means ascending (A to Z, small to large). Default if omitted.
• DESC means descending (Z to A, large to small).
• You can sort by multiple columns. The database compares the first, then breaks ties using the second, then the third, and so on.
• NULL sorting differs by database. If your system lacks explicit NULLS LAST/NULLS FIRST syntax, use CASE or COALESCE to control placement.

ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END, amount DESC

ORDER BY COALESCE(amount, 0) DESC

Mental model

Think of ORDER BY like a multi-key spreadsheet sort. The database lines up rows by the first column; when values tie, it looks to the next column, and so on. If you don’t specify a tie-breaker, the order of tied rows is not guaranteed.

Syntax patterns you’ll use

• Sort by one column (ascending by default):

  SELECT *
  FROM products
  ORDER BY product_name;

• Sort by multiple columns and directions:

  SELECT order_id, total_amount, order_date
  FROM orders
  ORDER BY total_amount DESC, order_date ASC;

• Sort by an expression or alias:

  SELECT order_id,
         total_amount,
         EXTRACT(YEAR FROM order_date) AS order_year
  FROM orders
  ORDER BY order_year, total_amount DESC;

• Custom business priority with CASE:

  SELECT name, segment, mrr
  FROM accounts
  ORDER BY CASE segment
             WHEN 'Enterprise' THEN 1
             WHEN 'SMB' THEN 2
             ELSE 3
           END,
           mrr DESC;

• Control NULL ordering (portable approach):

  SELECT id, amount
  FROM payments
  ORDER BY CASE WHEN amount IS NULL THEN 1 ELSE 0 END,
           amount DESC;

• Top-N after sorting (ANSI-standard):

  SELECT order_id, total_amount
  FROM orders
  ORDER BY total_amount DESC
  FETCH FIRST 5 ROWS ONLY;  -- similar to LIMIT 5 in many systems

Worked examples

-- Goal: last name A→Z, then first name A→Z
SELECT first_name, last_name
FROM customers
ORDER BY last_name ASC, first_name ASC;

-- Goal: high to low by amount; tie-break by order_date oldest first
SELECT order_id, total_amount, order_date
FROM orders
ORDER BY total_amount DESC, order_date ASC;

SELECT product_id, category, price
FROM products
ORDER BY category ASC,
         CASE WHEN price IS NULL THEN 1 ELSE 0 END,
         price ASC;

How to build a correct ORDER BY (quick steps)

1. Decide the main business priority (e.g., revenue DESC, date DESC).
2. List tie-breakers in order (e.g., customer_name ASC, id ASC).
3. Handle NULLs explicitly (CASE/COALESCE) if needed.
4. Test with a small sample to confirm the exact order.
5. Document the rationale in a comment for teammates.

Exercises (mirrors the Practice panel)

Run these on scratch tables or your own sandbox. Each has a hint and a solution you can reveal.

Exercise 1: Alphabetical customer list with tie-breakers

Table: customers(id, first_name, last_name, city)

-- Sample data
(1, 'Ava',  'Smith', 'Austin')
(2, 'Liam', 'Smith', 'Boston')
(3, 'Noah', 'Allen', 'Austin')
(4, 'Emma', 'Jones', 'Denver')
(5, 'Mia',  'Smith', 'Austin')

Task: Return first_name, last_name sorted by last_name ASC, then first_name ASC.

SELECT first_name, last_name
FROM customers
ORDER BY last_name ASC, first_name ASC;

Exercise 2: Revenue leaderboard with NULLs last

Table: orders(id, customer, total_amount)

-- Sample data
(101, 'A Co',  250.00)
(102, 'B Co',  NULL)
(103, 'C Co',  980.50)
(104, 'D Co',  980.50)
(105, 'E Co',   75.00)
(106, 'F Co',  NULL)

Task: Return id, customer, total_amount sorted by total_amount DESC with NULLs last; break ties by id ASC.

SELECT id, customer, total_amount
FROM orders
ORDER BY CASE WHEN total_amount IS NULL THEN 1 ELSE 0 END,
         total_amount DESC,
         id ASC;

Exercise 3: Custom business priority

Table: accounts(id, name, segment, mrr)

-- Sample data
(1, 'Apex Corp',   'Enterprise', 12000)
(2, 'Bright LLC',  'SMB',          900)
(3, 'CityWorks',   'Enterprise',  7000)
(4, 'Dainty Co',   'Other',        400)
(5, 'EverFit',     'SMB',         1200)

Task: Sort by segment priority Enterprise first, then SMB, then Others; within each, mrr DESC.

SELECT name, segment, mrr
FROM accounts
ORDER BY CASE segment
           WHEN 'Enterprise' THEN 1
           WHEN 'SMB' THEN 2
           ELSE 3
         END,
         mrr DESC;

Exercise checklist

• Did you set a clear primary sort and tie-breakers?
• Did you handle NULLs explicitly if needed?
• Did you confirm direction (ASC/DESC) on each column?
• Did you test with data that includes ties and NULLs?

Common mistakes and how to self-check

• Assuming order without ORDER BY: Always add ORDER BY. Rerun without it to see how the order can change.
• Forgetting tie-breakers: If ties matter, include a stable secondary key (e.g., id ASC).
• Incorrect NULL handling: Explicitly push NULLs first/last with CASE or COALESCE.
• Sorting numbers stored as text: '100' comes before '20'. Cast to numeric if needed.
• Relying on column positions: ORDER BY 2 breaks when SELECT list changes. Prefer column names.
• Collation/case surprises: If 'a' and 'A' order oddly, normalize with LOWER(column) in ORDER BY.

Mini challenge

You have tickets(id, priority, created_at, assignee). Sort by priority High > Medium > Low, then oldest created_at first, then assignee A→Z. Write one query that does this with a CASE expression. Verify with sample rows including ties.

Who this is for

• Business Analysts preparing stakeholder-ready tables and exports
• Early-career analysts needing predictable, auditable result ordering
• Anyone new to SQL who wants clean, ranked outputs

Prerequisites

• Basic SELECT and FROM usage
• Comfort with column names, aliases, and simple expressions
• Optional: familiarity with CASE and COALESCE

Learning path

• Before: Selecting columns, simple filtering with WHERE
• Now: Sorting with ORDER BY (this lesson)
• Next: Limiting rows (FETCH FIRST/LIMIT), Aggregating with GROUP BY, Joining tables

Practical projects

• Executive dashboard export: Top 20 customers by revenue this month, ties broken by most recent order.
• Support queue: Sorted list by custom priority rules and oldest creation time.
• Product catalog: Category A→Z, then price low→high, unknown prices last.

Next steps

• Refactor one of your existing reports to add explicit tie-breakers.
• Create a small seed dataset with NULLs and ties to test your sorting rules.
• Document your business ordering logic (why each key and direction matters).

Quick test

Take the quick test below. Everyone can take it for free; only logged-in users will have their progress saved.