ORDER BY: Sort Results

The Orange Cap table lists batters from most runs to fewest. Without sorting, a database returns rows in whatever order is fastest for it — which is not guaranteed to be meaningful. ORDER BY puts the result in the order you want.

Definition

ORDER BY sorts the final result set by one or more columns or expressions. ASC sorts lowest → highest (A → Z, oldest → newest) and is the default; DESC sorts highest → lowest. It is the only way to guarantee the order of rows in a result.

Syntax

SELECT column_list
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];

Example — highest first (DESC)

SELECT *
FROM players
ORDER BY runs DESC;

Execution order: 1️⃣ FROM → 2️⃣ SELECT * → 3️⃣ ORDER BY runs DESC. Like the Orange Cap race!

Example — lowest first (ASC)

SELECT *
FROM players
ORDER BY runs ASC;

ORDER BY runs without ASC gives exactly the same result, because ASC is the default.

Example — two columns

SELECT *
FROM players
ORDER BY team ASC, runs DESC;

How it works: rows are first sorted by team alphabetically (CSK, DC, MI, RCB, RR). Only where the team is the same — the two MI players — does the second column decide: Rohit (500) comes before Bumrah (20) because runs is DESC. Each column has its own direction.

Sorting with NULLs

In SQL Server, NULL is treated as the lowest value: NULLs appear first with ASC and last with DESC. In the innings table, Dhoni's 31 March innings has runs = NULL:

SELECT TOP 4 player, runs
FROM innings
ORDER BY runs ASC;

Sorting by an alias

Because ORDER BY runs after SELECT, it can use a column alias:

SELECT name, runs * 2 AS double_runs
FROM players
ORDER BY double_runs DESC;

Common mistakes

• ❌ Assuming rows come back in insert order or id order. ✅ No ORDER BY = no guaranteed order.
• ❌ ORDER BY team, runs DESC and expecting both descending. ✅ DESC applies only to the column right before it — write team DESC, runs DESC.
• ❌ ORDER BY 3 DESC (column position) — it works, but breaks silently when the SELECT list changes. ✅ Use column names.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: Without ORDER BY, will SQL Server always return rows in primary-key order? A: No. It often looks like that on small tables, but the order depends on indexes, parallelism and the execution plan. The only guarantee of order is an ORDER BY on the outermost query.