ORDER BY

Learning Focus

Use this lesson to understand ORDER BY with practical syntax and examples.

Concept Overview

ORDER BY is a SQL clause that sorts query results based on one or more columns or expressions. It controls the final presentation order of a result set.

In PostgreSQL, ordering is not just for display. Correct ordering is required for:

• stable pagination
• "top N" queries (largest/smallest values)
• deterministic reporting outputs

Why is it important?

• User experience: lists, dashboards, and exports must be readable and consistent
• Business intelligence: ranking and trend analysis depend on correct ordering
• Correctness: pagination without deterministic order leads to duplicates/missing rows
• Performance: a good index can eliminate expensive sorts

Where does it fit?

ORDER BY is part of DQL (Data Query Language) and usually appears near the end of a query, after filtering and grouping:

1. FROM (choose input rows)
2. WHERE (filter rows)
3. GROUP BY / HAVING (aggregate)
4. SELECT (project columns)
5. ORDER BY (sort)
6. LIMIT / OFFSET (return a subset)

---

Syntax & Rules

Core Syntax

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
ORDER BY sort_expression1 [ASC|DESC] [NULLS FIRST|NULLS LAST],
         sort_expression2 [ASC|DESC] [NULLS FIRST|NULLS LAST]
[LIMIT number] [OFFSET offset];

Available Options / Parameters

Option	Meaning	Notes
ASC	ascending order	default if omitted
DESC	descending order	reverse ordering
NULLS FIRST	NULL values appear first	useful for missing data surfacing
NULLS LAST	NULL values appear last	common for dashboards
column positions	ORDER BY 2, 3 DESC	counts from select list (use sparingly)
expressions	ORDER BY salary * 1.1	can be slower without indexes
collations	ORDER BY name COLLATE "C"	impacts string ordering

Key Rules and Considerations

• ORDER BY processes sort keys left to right. The first key is primary; later keys break ties.
• Row order is undefined without ORDER BY.
• For stable pagination, include a unique tie-breaker (usually the primary key).
• Sorting large result sets can be expensive (disk spills). Filter first and limit results when possible.
• In PostgreSQL, you can explicitly control NULL placement with NULLS FIRST/LAST.

---

Step-by-Step Examples

The examples below use a realistic employees table.

-- Sample table creation
CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  department  text NOT NULL,
  salary      numeric(12,2) NOT NULL CHECK (salary >= 0),
  hire_date   date NOT NULL,
  performance_rating integer,
  commission_rate numeric(5,2)
);

-- Sample data insertion
INSERT INTO employees (full_name, department, salary, hire_date, performance_rating, commission_rate) VALUES
('Alice Johnson',  'Sales',       55000.00, '2022-01-15', 4, 0.05),
('Bob Smith',      'Engineering', 75000.00, '2021-03-20', 5, NULL),
('Carol Williams', 'Marketing',   48000.00, '2022-06-10', 3, NULL),
('David Brown',    'Engineering', 82000.00, '2020-11-05', 4, 0.02),
('Eva Davis',      'Sales',       52000.00, '2021-09-12', 5, 0.04),
('Frank Miller',   'Marketing',   45000.00, '2023-02-28', 3, NULL);

Example 1: Simple Ascending Sort (Beginner)

-- Sort employees by name (A-Z)
SELECT full_name, department, salary
FROM employees
ORDER BY full_name ASC;

Expected output:

 full_name       | department  | salary
----------------+-------------+----------
 Alice Johnson   | Sales       | 55000.00
 Bob Smith       | Engineering | 75000.00
 Carol Williams  | Marketing   | 48000.00
 David Brown     | Engineering | 82000.00
 Eva Davis       | Sales       | 52000.00
 Frank Miller    | Marketing   | 45000.00
(6 rows)

Explanation:

• ORDER BY full_name sorts alphabetically.
• ASC is optional because it is the default.

---

Example 2: Descending Sort (Beginner)

-- Sort employees by salary (highest to lowest)
SELECT full_name, department, salary
FROM employees
ORDER BY salary DESC;

Expected output:

 full_name       | department  | salary
----------------+-------------+----------
 David Brown     | Engineering | 82000.00
 Bob Smith       | Engineering | 75000.00
 Alice Johnson   | Sales       | 55000.00
 Eva Davis       | Sales       | 52000.00
 Carol Williams  | Marketing   | 48000.00
 Frank Miller    | Marketing   | 45000.00
(6 rows)

Explanation:

• DESC reverses the sort order.

---

Example 3: Multiple Column Sort (Intermediate)

-- Sort by department first, then salary within each department
SELECT full_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Expected output:

 full_name       | department  | salary
----------------+-------------+----------
 David Brown     | Engineering | 82000.00
 Bob Smith       | Engineering | 75000.00
 Carol Williams  | Marketing   | 48000.00
 Frank Miller    | Marketing   | 45000.00
 Alice Johnson   | Sales       | 55000.00
 Eva Davis       | Sales       | 52000.00
(6 rows)

Explanation:

• Primary sort is department.
• Within each department, salary is sorted descending.

---

Example 4: Sorting with NULLS FIRST/LAST (Intermediate)

By default, NULL ordering depends on direction. PostgreSQL lets you control it explicitly.

-- Sort commission rates with NULLs last
SELECT full_name, commission_rate
FROM employees
ORDER BY commission_rate ASC NULLS LAST, full_name ASC;

Expected output:

 full_name      | commission_rate
---------------+-----------------
 David Brown    | 0.02
 Eva Davis      | 0.04
 Alice Johnson  | 0.05
 Bob Smith      |
 Carol Williams |
 Frank Miller   |
(6 rows)

Explanation:

• NULLS LAST makes missing values appear at the bottom.
• Adding full_name as a secondary key ensures deterministic ordering for ties.

---

Example 5: Complex Sort with Expressions (Advanced)

Sort by performance rating, then by experience (derived from hire_date).

-- Sort by performance rating, then by "years of experience" (approx)
SELECT
  full_name,
  department,
  performance_rating,
  hire_date,
  round(extract(epoch from (now()::date - hire_date)) / (365 * 24 * 60 * 60), 2) AS years_experience
FROM employees
ORDER BY performance_rating DESC,
         years_experience DESC,
         full_name ASC;

Expected output:

 full_name       | department  | performance_rating | hire_date   | years_experience
----------------+-------------+--------------------+-------------+-----------------
 Bob Smith       | Engineering | 5                  | 2021-03-20  | 4.0
 Eva Davis       | Sales       | 5                  | 2021-09-12  | 3.5
 David Brown     | Engineering | 4                  | 2020-11-05  | 4.3
 Alice Johnson   | Sales       | 4                  | 2022-01-15  | 3.1
 Carol Williams  | Marketing   | 3                  | 2022-06-10  | 2.7
 Frank Miller    | Marketing   | 3                  | 2023-02-28  | 2.0
(6 rows)

Explanation:

• Expressions in ORDER BY can be expensive at scale.
• If you need this frequently, consider storing a derived value or using indexed columns.

---

Practical Use Cases

1. E-commerce Product Listings

Business problem: Show best-rated items first; break ties with sales and then price.

SELECT product_id, product_name, rating, sales_count, price
FROM products
WHERE category = 'Electronics'
ORDER BY rating DESC, sales_count DESC, price ASC
LIMIT 50;

2. Employee Performance Ranking (HR)

Business problem: Build a ranking list for annual reviews.

SELECT full_name, department, performance_rating, salary
FROM employees
ORDER BY performance_rating DESC, salary ASC;

3. Time-Series Reporting (Finance)

Business problem: Show latest months first in a revenue report.

SELECT date_trunc('month', created_at) AS month_key, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY month_key DESC;

4. Support Ticket Priority Queue (Support)

Business problem: Work oldest high-priority tickets first.

SELECT ticket_id, priority_level, created_at, subject
FROM support_tickets
WHERE status = 'open'
ORDER BY priority_level DESC, created_at ASC
LIMIT 200;

5. Inventory Restock List (Operations)

Business problem: Sort most urgent restocks first.

SELECT sku, product_name, current_stock, reorder_level,
       (current_stock - reorder_level) AS stock_diff
FROM inventory
WHERE current_stock <= reorder_level
ORDER BY stock_diff ASC, product_name ASC;

---

Common Mistakes & Troubleshooting

Mistake 1: Sorting without ORDER BY (assumed order)

Wrong SQL:

SELECT full_name, salary
FROM employees
LIMIT 3;

Bad outcome: the "first 3" rows are not guaranteed across runs.

Fix:

SELECT full_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 3;

Mistake 2: Pagination without a stable tie-breaker

Wrong SQL:

SELECT employee_id, full_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2 OFFSET 2;

Bad outcome: rows can move between pages when salaries tie.

Fix:

SELECT employee_id, full_name, salary
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 2 OFFSET 2;

Mistake 3: Slow ORDER BY on large tables

Wrong SQL:

SELECT *
FROM large_orders
ORDER BY created_at DESC;

Bad outcome: long-running sort (may spill to disk).

Fix: add a supporting index and/or limit the result.

CREATE INDEX idx_large_orders_created_at ON large_orders (created_at DESC);

SELECT order_id, created_at
FROM large_orders
ORDER BY created_at DESC
LIMIT 100;

Mistake 4: Unexpected NULL positioning

Wrong assumption:

SELECT full_name, commission_rate
FROM employees
ORDER BY commission_rate;

Bad outcome: NULLs may appear before actual values.

Fix:

SELECT full_name, commission_rate
FROM employees
ORDER BY commission_rate ASC NULLS LAST;

Debugging Tips

1. Confirm the sort keys: write them explicitly and add tie-breakers.
2. If results are inconsistent, add a unique key at the end of ORDER BY.
3. If performance is slow, run EXPLAIN (ANALYZE, BUFFERS) and check for Sort nodes.
4. Check indexes: if you sort by (a, b), consider an index on (a, b).

---

Best Practices

✅ Always use ORDER BY for user-facing lists and reports.
✅ Always include a unique tie-breaker for pagination (primary key).
✅ Always filter rows before sorting when possible (WHERE first).
✅ Always use NULLS LAST/FIRST explicitly when NULL positioning matters.
✅ Always validate performance with EXPLAIN before adding indexes.
❌ Avoid sorting huge result sets without LIMIT.
❌ Avoid ordering by complex expressions repeatedly; precompute or index appropriately.
❌ Avoid relying on natural/physical row order.

---

Hands-On Practice

Exercise 1 (Easy)

Task: List all employees sorted by hire date (newest first).

-- Write your query here

Solution:

SELECT full_name, hire_date
FROM employees
ORDER BY hire_date DESC;

Exercise 2 (Medium)

Task: Sort employees by department, and within each department by salary (highest first).

-- Write your query here

Solution:

SELECT full_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC, employee_id ASC;

Exercise 3 (Advanced)

Task: Return the top 3 employees by performance rating, breaking ties by earliest hire date.

-- Write your query here

Solution:

SELECT full_name, performance_rating, hire_date
FROM employees
ORDER BY performance_rating DESC, hire_date ASC, employee_id ASC
LIMIT 3;

---

Connection to Other Concepts

Related lesson	Why it matters
WHERE Clause	Filtering reduces the amount of data that must be sorted
LIMIT	ORDER BY + LIMIT is the standard top-N pattern
Index Strategy	Indexes can avoid sorts or reduce their cost
Window Functions	Ranking functions depend on ordering
Performance Optimization	EXPLAIN shows whether sorting is a bottleneck

---

Visual Learning Diagram

flowchart LR
  A[SELECT + WHERE] --> B[ORDER BY]
  B --> C[LIMIT / OFFSET]
  B --> D[Top-N Reports]
  B --> E[Stable Pagination]
  B --> F[Index Design]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5

  class A,C,D,E,F allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Missing tie-breaker for pagination	Duplicate/missing rows across pages	Add primary key as last ORDER BY key
Sorting without LIMIT on large tables	Slow queries and disk spills	Add LIMIT or filter more aggressively
Unexpected NULL positioning	Reports put missing values first	Use NULLS FIRST/LAST explicitly
Ordering by expression repeatedly	CPU-heavy and not indexable by default	Add expression index or store derived value
Relying on implicit row order	Inconsistent results	Always specify ORDER BY

---

Quick Reference

SELECT * FROM t ORDER BY created_at DESC;
SELECT * FROM t ORDER BY score DESC, id ASC;
SELECT * FROM t ORDER BY col ASC NULLS LAST;
SELECT * FROM t ORDER BY 2, 3 DESC;
SELECT * FROM t WHERE status='open' ORDER BY priority DESC, created_at ASC LIMIT 100;

What's Next

• Previous: WHERE Clause - Review the previous lesson to reinforce context.
• Next: INSERT INTO - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.