SQL ORDER BY – Sorting Query Results

ORDER BY Syntax

ORDER BY is placed at the end of a SELECT statement, after WHERE (if present). You specify the column to sort by, followed by the direction.

SQL

SELECT column1, column2
FROM table_name
WHERE condition          -- optional
ORDER BY sort_column [ASC | DESC];

ℹ️

ORDER BY runs AFTER WHERE and SELECT

In the logical execution order, ORDER BY is the last clause to run. This means you can use column aliases defined in SELECT inside ORDER BY — because by that point, the aliases have already been established. This is the opposite of WHERE, which runs before SELECT.

ASC and DESC

The two sort directions are:

ASC (ascending) — smallest to largest, A to Z, oldest to newest. This is the default — you can omit it.
DESC (descending) — largest to smallest, Z to A, newest to oldest.

SQL

-- Sort by salary ascending (lowest first) — ASC is the default
SELECT first_name, salary
FROM employees
ORDER BY salary;

-- Same query, ASC explicit
SELECT first_name, salary
FROM employees
ORDER BY salary ASC;

-- Sort by salary descending (highest first)
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

-- Sort alphabetically by first name (A → Z)
SELECT first_name, department
FROM employees
ORDER BY first_name ASC;

-- Get the 5 most recent orders
SELECT id, order_date, total
FROM orders
ORDER BY order_date DESC;

Ordering by Multiple Columns

You can sort by more than one column, separated by commas. The database sorts by the first column first; rows that have the same value in the first column are then sorted by the second column, and so on.

SQL

-- Sort by department A→Z, then by salary high→low within each department
SELECT first_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

-- Result order: Engineering employees (Carol 91k, Alice 85k, Eve 77k),
--               then Marketing employees (Bob 62k, David 58k)

-- Sort by last name, then first name (tie-breaker)
SELECT first_name, last_name
FROM customers
ORDER BY last_name ASC, first_name ASC;

-- Each column can have its own direction
SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

Sorting by Expressions

You can sort by any expression — arithmetic, string functions, or even a column alias defined in SELECT.

SQL

-- Sort by a calculated expression (annual salary)
SELECT first_name, salary, salary * 12 AS annual_salary
FROM employees
ORDER BY salary * 12 DESC;

-- Sort using the column alias (works in ORDER BY because it runs after SELECT)
SELECT first_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

-- Sort by string length (shortest name first)
SELECT first_name
FROM employees
ORDER BY LENGTH(first_name) ASC;

-- Sort by year and month extracted from a date
SELECT id, order_date
FROM orders
ORDER BY YEAR(order_date) DESC, MONTH(order_date) DESC;

NULL Ordering

What happens to NULL values when you sort? Different databases handle this differently:

Most databases (PostgreSQL, SQLite, SQL Server): NULLs sort as if they are smaller than any non-NULL value — so NULLs come first in ASC and last in DESC.
MySQL and MariaDB: NULLs also sort first in ASC (treated as less than any value), last in DESC.

SQL

-- In most databases: NULLs appear first when sorting ASC
SELECT first_name, manager_id
FROM employees
ORDER BY manager_id ASC;
-- Alice (NULL) appears first, then Bob (1), Carol (1), David (2), Eve (3)

-- PostgreSQL: control NULL position with NULLS FIRST / NULLS LAST
SELECT first_name, manager_id
FROM employees
ORDER BY manager_id ASC NULLS LAST;   -- push NULLs to the end

SELECT first_name, manager_id
FROM employees
ORDER BY manager_id DESC NULLS FIRST; -- keep NULLs at the top

-- MySQL workaround: use ISNULL() to push NULLs to the end
SELECT first_name, manager_id
FROM employees
ORDER BY ISNULL(manager_id) ASC, manager_id ASC;

💡

Always test NULL sorting in your specific database

NULLS FIRST and NULLS LAST are part of the SQL standard and supported by PostgreSQL and SQLite. MySQL does not support this syntax — use the ISNULL(column) workaround shown above. When NULL ordering matters to your application, test it explicitly rather than relying on default behaviour.

ORDER BY: Sorting, and Where NULLs Land

ORDER BY sorts the result — and unlike WHERE, it runs after SELECT, so it can use column aliases. Sort by multiple keys for tie-breaking: the second key only decides rows equal on the first.

SELECT name, price * 1.1 AS total
FROM products
ORDER BY category ASC,     -- primary sort
         total DESC;       -- tie-breaker; alias works here (not in WHERE)

Clause	Effect
`ASC` (default)	ascending A→Z, 0→9
`DESC`	descending
`NULLS LAST`	push missing values to the end

NULL ordering differs by database. PostgreSQL sorts NULLs last in ascending order; MySQL sorts them first. When it matters, be explicit with ORDER BY col ASC NULLS LAST (or emulate it). Sorting by a column not in SELECT is allowed. And remember: without ORDER BY, row order is not guaranteed — never rely on "it happened to come out sorted." Combine with LIMIT for top-N queries.

🏋️ Practical Exercise

Sort results ascending with ORDER BY.
Sort descending with DESC.
Sort by two columns.
Sort by a column alias.
Sort by column position number.

🔥 Challenge Exercise

Query a products table sorted by category ascending, then price descending within each category. Explain the default sort direction and how multi-column ordering is applied.

📋 Summary

Without ORDER BY, row order is undefined and unreliable — always sort when order matters.
ORDER BY column ASC sorts smallest to largest (default). ORDER BY column DESC sorts largest to smallest.
Sort by multiple columns by separating them with commas — later columns act as tie-breakers.
You can sort by expressions and column aliases defined in SELECT (ORDER BY runs after SELECT).
NULLs sort first in ASC in most databases. Use NULLS FIRST / NULLS LAST (PostgreSQL/SQLite) or the ISNULL() workaround (MySQL) to control NULL position.

Interview Questions

What does ORDER BY do?
What is the default sort direction?
How do you sort by multiple columns?
Can you sort by a column not in the SELECT list?
How does ORDER BY handle NULL values?

FAQ

Does ORDER BY affect query performance? +

Yes — sorting requires work. For small result sets (a few thousand rows) the overhead is negligible. For large result sets, sorting can be expensive because the database must process all filtered rows and sort them before returning the first result. If you have a covering index on the sort column, the database can sometimes use the index order directly and avoid an explicit sort step. For most typical queries, ORDER BY is fine — just be aware of performance on very large unsorted result sets.

Can I use ORDER BY with a column number instead of a name? +

Yes — ORDER BY 2 DESC sorts by the second column in the SELECT list. This syntax is supported by all major databases. It is convenient for quick queries in a database client, but it is a bad practice in application code because if you change the column order in SELECT, the sort column silently changes too. Always use column names in code that you maintain long-term.

Is the result order guaranteed without ORDER BY? +

No — without ORDER BY, the SQL standard explicitly states that row order is undefined. In practice, databases often return rows in the order they were inserted or in index scan order, which can look predictable during development. But this is an implementation detail that can change with query plan changes, data distribution, or database upgrades. Never rely on implicit ordering in production code — if order matters, always use ORDER BY.

Can I use ORDER BY with GROUP BY? +

Yes. ORDER BY runs after GROUP BY. You can sort by any column that appears in the GROUP BY clause or by an aggregate expression in the SELECT list. For example: SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary DESC; groups by department, calculates the average salary per group, then sorts the groups from highest to lowest average salary.