Module 10 — ORDER BY

1. ORDER BY Basics

What it is: ORDER BY sorts the query result by one or more columns. Without it, PostgreSQL returns rows in an unpredictable order (typically insertion order, but not guaranteed).

Why we use it: Users expect sorted data — products by price, users by name, orders by date. ORDER BY ensures consistent, predictable results every time you run the query.

When we use it: Every time data needs to be displayed in a specific order — product listings, search results, reports, dashboards, or any paginated output.

-- Sort by price (lowest to highest) — ASC is the default
SELECT * FROM products ORDER BY price;
 
-- Sort by price (highest to lowest)
SELECT * FROM products ORDER BY price DESC;
 
-- Explicitly specify ascending
SELECT * FROM products ORDER BY price ASC;

2. Multi-Column Sort

What it is: Multi-column sorting applies a secondary sort when the primary sort has ties. For example, sort by category first, then by price within each category.

Why we use it: Single-column sorting often isn't enough — you might want products grouped by category, but within each category, sorted by price. Multi-column sort handles this in one query.

When we use it: When primary sort values have duplicates, when displaying hierarchical data, or when building complex report layouts.

-- Sort by category, then by price within each category
SELECT * FROM products
ORDER BY category ASC, price DESC;

How it works

1. First, all rows are sorted by category (A → Z)
2. Within each category, rows are sorted by price (highest → lowest)

3. Sort by Column Position

What it is: Instead of typing the column name, you can reference columns by their position in the SELECT list (1 for the first column, 2 for the second, etc.).

Why we use it: It's a shortcut for quick queries, especially when sorting by computed columns that don't have aliases.

When we use it: In ad-hoc queries and quick explorations. Avoid in production code — column positions can change if someone modifies the SELECT list.

-- Sort by the 2nd column (price)
SELECT name, price FROM products ORDER BY 2 DESC;

4. Sort by Expression or Alias

What it is: You can sort by computed expressions (like price * 0.9) or by column aliases defined in the SELECT clause. This lets you sort by values that don't exist in the table.

Why we use it: Sometimes the sort criteria is derived — discounted price, string length, age from birthdate, etc. Sorting by expression handles this without storing the computed value.

When we use it: When sorting by derived values, when using computed columns, or when building reports that need custom sort orders.

-- Sort by a computed value
SELECT name, price, price * 0.9 AS discounted
FROM products
ORDER BY discounted DESC;
 
-- Sort by string length
SELECT name FROM products ORDER BY LENGTH(name) DESC;

5. NULLS FIRST / NULLS LAST

What it is: By default, NULL values sort as if they are "greater than" any non-NULL value (in ascending order, NULLs appear last). NULLS FIRST and NULLS LAST let you override this behavior.

Why we use it: Depending on the use case, you might want NULLs at the top (to find missing data quickly) or at the bottom (to show complete records first).

When we use it: When building forms that show incomplete records first, when prioritizing data quality, or when business logic requires NULLs in a specific position.

-- NULLs appear last (default in ascending order)
SELECT * FROM users ORDER BY bio ASC NULLS LAST;
 
-- NULLs appear first
SELECT * FROM users ORDER BY bio ASC NULLS FIRST;

Common Mistakes to Avoid

1. Relying on default order — Without ORDER BY, row order is unpredictable
2. Sorting before LIMIT — Always ORDER BY before LIMIT to get consistent pages
3. Sorting large result sets — Sorting millions of rows is expensive. Use indexes.

Practice Exercise

Using your courses table:

1. Select all courses sorted by price (lowest to highest)
2. Select all courses sorted by price (highest to lowest)
3. Sort courses by instructor name (A → Z), then by price (highest → lowest)
4. Select course title and price, sort by price descending
5. Sort courses with NULL instructors first, then by name