Alright, let’s talk about ORDER BY. This is where you stop just grabbing data and start imposing some actual order on the chaos. It’s the difference between a pile of ingredients and a prepared meal. The ORDER BY clause is your way of telling the database, “I don’t just want the data; I want it like this.”

The basic syntax is brain-dead simple: ORDER BY column_name. But the devil, and the real power, is in the details.

The Basics: ASC and DESC

By default, sorting is ascending (ASC). You don’t even have to type it. But the moment you want the highest value at the top, you need its evil twin, DESC (descending).

-- Let's find our top-selling products. Because money talks.
SELECT product_name, units_sold
FROM products
ORDER BY units_sold DESC;

This seems obvious, but here’s the first pitfall: the sort is based on the data type. Sorting a text column (VARCHAR) descending will give you Z-A, which is rarely what anyone actually wants for a report. It’s great for putting “Zucchini” at the top of your vegetable list, I guess. For numbers and dates, DESC is far more commonly useful.

The NULL Problem and NULLS FIRST/LAST

Here’s where things get interesting, and where most beginners get a nasty surprise. Where do NULL values go?

The SQL standard, in its infinite wisdom, decided that NULL represents an unknown value. So when you ask to sort, the database has a crisis of conscience. Is an unknown value greater or less than 37? It can’t say! The answer is… it depends on the database.

PostgreSQL, being the beautifully pedantic system it is, gives you explicit control. By default, NULLs are considered larger than all other values when sorting in ascending order. So they appear at the end. But you can override this with NULLS FIRST or NULLS LAST.

-- Who hasn't reported their expense total? I want to see them first.
SELECT employee_id, expense_total
FROM expense_reports
ORDER BY expense_total NULLS FIRST;

-- Now let's see the actual spenders, biggest first, and forget the slackers.
SELECT employee_id, expense_total
FROM expense_reports
ORDER BY expense_total DESC NULLS LAST;

If you’re not using PostgreSQL, check your database’s documentation. MySQL, for instance, always sorts NULLs first in ascending order and last in descending order, whether you like it or not. It’s a classic “questionable choice” that you just have to know and work around.

Sorting by Multiple Columns: Order of Operations

This is the real pro move. You almost never sort by just one column. You want a primary sort and then a tie-breaker. The ORDER BY clause is processed from left to right.

Think of it like sorting a deck of cards: first by suit (e.g., all clubs together, then diamonds, etc.), and then within each suit, by rank.

-- Let's get a sensible employee directory.
SELECT last_name, first_name, department
FROM employees
ORDER BY department ASC, last_name ASC, first_name ASC;

This sorts everyone first by their department (A-Z), then within each department, by last name (A-Z), and if two people in the same department have the same last name (looking at you, Marketing), it sorts by their first name. The order of your columns is absolutely critical. Swapping last_name and department would give you a completely useless list.

You can mix and match ASC/DESC and NULLS directives on each column independently.

-- Show me the highest salaries in each department, and list people
-- with no salary reported at the bottom of each department group.
SELECT department, salary, last_name
FROM employees
ORDER BY department ASC, salary DESC NULLS LAST, last_name ASC;

You Can Sort by Things You Don’t Select

This is a common point of confusion. Your ORDER BY clause has access to all the columns available to the SELECT statement at that point, including ones you don’t ultimately output. This is incredibly useful.

-- I want a clean list of names, but I want it sorted by a hidden column.
SELECT first_name, last_name
FROM employees
ORDER BY hire_date DESC; -- Show me the newest hires first

Just be careful: once you start using GROUP BY or aliases, the rules change slightly. You can often only ORDER BY columns that are in the GROUP BY clause or aggregate functions. But that’s a fight for another chapter.

Sorting by Ordinal Position (And Why You Shouldn’t)

You can technically ORDER BY 1, 2, meaning “sort by the first column I selected, then the second.” Please, for the love of all that is holy, don’t do this in real code. It’s a maintenance nightmare. If someone changes the SELECT clause later and doesn’t meticulously check the ORDER BY, the entire sort order explodes silently. Always use the explicit column name. It’s self-documenting and safe. The only exception is when you’re hacking together a one-off query at 2 AM and your coffee has gone cold. I won’t tell anyone. This time.