SQL online course

SQL ORDER BY Clause: Sort Query Results

Learn how to use SQL ORDER BY to sort query results by one or more columns in ascending or descending order.

The SQL ORDER BY clause controls the presentation order of rows in a query result. The returned rows are called a result set.

Database tables do not have a guaranteed natural row order. Without ORDER BY, a database may return rows in any order, even if the same query appeared sorted previously. Use ORDER BY whenever the display order matters.

You can sort by one column or by multiple columns. A column or expression used to determine row order is called a sort key.

For background on retrieving columns and rows, see the SQL SELECT statement.

ORDER BY Syntax

A basic query places ORDER BY after the FROM clause:

SELECT column_list
FROM table_name
ORDER BY column_name [ASC | DESC];

An order-by item contains a selected or available column, or an expression, followed optionally by a direction:

SELECT column_name1, column_name2
FROM table_name
ORDER BY column_name1 ASC, column_name2 DESC;

ASC and DESC are optional direction keywords. Separate multiple sort keys with commas.

Ascending Order with ASC

ASC means ascending order. It is the default direction, so leaving out the direction has the same usual effect as writing ASC.

• Text is typically ordered alphabetically, from A to Z.
• Numbers are ordered from low to high.
• Dates are ordered from earliest to latest.

SELECT employeeNumber, firstName, lastName
FROM employees
ORDER BY firstName ASC;

This explicitly sorts employees by firstName in ascending alphabetical order. The following query normally produces the same direction:

SELECT *
FROM employees
ORDER BY firstName;

Because ASC is the default, omitting it does not mean descending order.

Descending Order with DESC

DESC sorts in reverse order:

• Text is typically ordered from Z to A.
• Numbers are ordered from high to low.
• Dates are ordered from latest to earliest.

SELECT employeeNumber, firstName, lastName
FROM employees
ORDER BY employeeNumber DESC;

This places the employee with the highest employee number first.

Sorting by One Column

A single-column sort uses one sort key. For example:

SELECT *
FROM employees
ORDER BY firstName;

The result contains complete employee rows, arranged alphabetically by firstName. The selected columns do not need to be listed in the same order as the sort key; ORDER BY determines the row order independently of the column display order.

The table illustrates the main sort key: the firstName values progress from Gerard to Julie to Leslie. If two employees have the same first name, their relative order is not guaranteed unless another sort key is supplied.

Sorting by Multiple Columns

Use commas to provide multiple sort keys:

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY lastName ASC, firstName ASC;

SQL applies the keys in precedence order:

1. The first item, lastName, is the primary sort column. It determines the main grouping and ordering.
2. If two rows have different last names, firstName does not affect their order.
3. If two rows have the same last name, firstName becomes the secondary sort column and breaks that tie.

Each sort key can have its own direction. This example sorts surnames from A to Z, then employee numbers from high to low only among rows with the same surname:

SELECT employeeNumber, lastName, firstName
FROM employees
ORDER BY lastName ASC, employeeNumber DESC;

The direction for employeeNumber does not reverse the entire result. It applies when two rows have equal lastName values.

How to Read a Sorted Result

To identify the ordering, inspect the first expression after ORDER BY. That is the primary sort key. Check whether its values move ascending or descending. For equal values, inspect the next sort key.

For example, in ORDER BY lastName ASC, firstName ASC, all rows are first grouped by surname. Only rows sharing a surname are then compared by given name. A secondary key makes the display order meaningful and predictable for those duplicate primary values.

Common Problems and Fixes

Results are not in the expected order

Cause: No ORDER BY clause was supplied. The database is free to return rows in any order.

Fix: Add the column or columns that define the required display order:

SELECT *
FROM employees
ORDER BY lastName ASC, firstName ASC;

A query without ASC did not sort descending

Cause: ASC is the default direction.

Fix: Write DESC after the relevant sort key:

SELECT *
FROM employees
ORDER BY employeeNumber DESC;

Rows with the same last name have an unexpected order

Cause: Only lastName was used, so ties were unresolved.

Fix: Add a secondary key such as firstName or employeeNumber:

SELECT *
FROM employees
ORDER BY lastName ASC, firstName ASC, employeeNumber ASC;

The secondary direction seems to affect every row

Cause: The primary and secondary keys are being treated as equal priorities.

Fix: Remember that SQL completes the primary ordering first. Later keys are considered only when earlier keys tie.

ORDER BY with Other Query Clauses

WHERE filters rows before the remaining result is sorted. For example:

SELECT employeeNumber, firstName, lastName
FROM employees
WHERE lastName = 'Bondur'
ORDER BY firstName ASC;

Learn more about filtering with the SQL WHERE clause. To return only part of an ordered result, see SQL SELECT LIMIT.

When sorting a calculated or displayed expression, a column alias can sometimes make the query easier to read. Review SQL aliases for naming expressions and result columns.

Key Points

• ORDER BY controls the presentation order of a result set.
• Rows have no guaranteed natural order without ORDER BY.
• ASC means ascending and is the default.
• DESC means descending.
• Multiple sort keys are separated by commas.
• The first key has priority; later keys resolve ties.
• Use a secondary key when duplicate primary values need a stable, meaningful order.