Lesson 06 of 61

5 mins read

SQL ORDER BY: Sorting, Multi-Column & NULLS Ordering

In Plain English

By relational database theory, tables are unordered mathematical sets: rows have NO guaranteed order unless explicitly requested with `ORDER BY`. You can sort by one or more columns in Ascending (`ASC`, default) or Descending (`DESC`) order.

The Mental Model

"Without `ORDER BY`, records in a database are like numbered tennis balls loose inside a bucket. `ORDER BY` lines them up neatly on a numbered rack by color, weight, or number."

Deep Dive: How It Works

Ascending (ASC) vs Descending (DESC): `ASC` sorts numbers lowest to highest and strings A to Z; `DESC` reverses this order.

Multi-Column Sorting: `ORDER BY country ASC, score DESC` sorts primarily by country; within each country, rows are sorted by score highest to lowest.

NULL Sorting: In Postgres/Oracle, specify `NULLS FIRST` or `NULLS LAST` to control where null entries land.

Syntax Blueprint

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;

ORDER BY sorts result sets by prioritized primary and secondary column keys.

Core Rules to Remember

No Inherent Order: Never assume row insertion order is preserved; always use ORDER BY for deterministic ordering.

Default is ASC: `ORDER BY price` is completely identical to `ORDER BY price ASC`.

Positional Indexes: `ORDER BY 1, 2 DESC` sorts by projected column numbers (useful in ad-hoc queries).

Common Beginner Traps & How to Fix Them

Mistake:Relying on physical row insertion order without an ORDER BY clause.

Why it happens: Parallel scans, vacuuming, and page fragmentation alter physical disk storage order unpredictably.

How to fix: Always declare explicit `ORDER BY id ASC` or timestamp when sequence matters.

Pro Tip: Sorting is computationally expensive: ensure columns frequently used in ORDER BY have B-tree indexes.

Live Interactive Example

Hit Run Code to see it live

Multi-Column Sorting on Score and Name

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Rows are sorted first by division alphabetically, then points descending, then name alphabetically for ties.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Sort Employees by Department and Salary

Select `name`, `department`, `salary` from `salaries` ordered by `department ASC, salary DESC`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.