VMware ESXi and vSphere Cluster Management

Sort Query Results with ORDER BY in MySQL

Learn how to sort MySQL SELECT results with ORDER BY, ASC, DESC, and multiple sort keys for predictable output.

Why query results need explicit sorting

SELECT is a SQL statement used to retrieve rows and columns from a table. For example:

SELECT * FROM testtb;

Without an ORDER BY clause, MySQL does not guarantee the order of returned rows. Results may appear to follow insertion order or the current storage order, but that behavior is not a reliable rule. Query plans, indexes, table changes, and database operations can change the displayed sequence.

If the order matters, state it explicitly with ORDER BY.

What ORDER BY does

ORDER BY is the clause that specifies how rows in a query result are sorted. In a simple query, it comes after FROM and any filtering clauses such as WHERE.

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

The columns or expressions after ORDER BY are called sort keys. A sort key is a criterion used to arrange the result rows. You can sort by columns included in the result or by other columns available from the source table.

General syntax

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

Multiple sort keys are separated with commas. The direction keyword is optional for each key.

Sample table and data

The examples use a table named testtb with these columns:

name — text containing a first name
surname — text containing a surname
year — an integer year

Sample rows are:

Amy Bryant, 1991
Mark Smith, 1955
John von Neumann, 1921
Aaron Rogers, 1995
Brian Cormier, 1988

Inspecting the table is useful before adding an ordering rule:

SELECT * FROM testtb;

The rows might be displayed in the sequence in which they were inserted, but that sequence is not guaranteed. Treat this query as an inspection query, not as a request for a particular order.

Ascending order: the default

Ascending order means increasing order: typically A to Z for text and low to high for numbers. Ascending order is the default when no direction keyword is supplied.

SELECT *
FROM testtb
ORDER BY name;

The expected name sequence is:

Aaron
Amy
Brian
John
Mark

For text, this is alphabetic ascending order according to the column's collation. For numeric values, ascending order normally places smaller values before larger values.

SELECT name, surname, year
FROM testtb
ORDER BY year;

The years should run from 1921, 1955, 1988, 1991, to 1995.

Writing ASC explicitly

ASC is the SQL keyword for ascending order. These two queries request the same direction:

SELECT * FROM testtb ORDER BY name;

SELECT * FROM testtb ORDER BY name ASC;

Writing ASC explicitly can make the intended direction easier to read, especially when a query uses several sort keys.

Descending order with DESC

Descending order means decreasing order: typically Z to A for text and high to low for numbers. Use DESC after the sort key.

SELECT *
FROM testtb
ORDER BY name DESC;

The expected name sequence is:

Mark
John
Brian
Amy
Aaron

For numeric data, descending order places the highest value first:

SELECT name, surname, year
FROM testtb
ORDER BY year DESC;

The year sequence should be 1995, 1991, 1988, 1955, then 1921.

Common mistake: ORDER BY name does not mean descending order. Because ascending order is the default, add DESC when you want Z to A or high to low.

Sorting by multiple columns

A query can have more than one sort key. Separate the keys with commas:

SELECT name, surname, year
FROM testtb
ORDER BY surname ASC, name ASC;

MySQL applies the keys by precedence:

Rows are sorted by surname.
If two rows have the same surname, those tied rows are sorted by name.

The second key does not rearrange every row independently. It matters only inside groups tied on the first key.

Each key can have its own direction. This query sorts newest years first, then sorts names from A to Z when years are equal:

SELECT name, surname, year
FROM testtb
ORDER BY year DESC, name ASC;

Another example sorts by surname from A to Z and then name from Z to A:

SELECT *
FROM testtb
ORDER BY surname ASC, name DESC;

Reading and validating sorted output

To validate a sort, identify the first and last expected values and check the complete sequence. For example:

SELECT * FROM testtb ORDER BY name ASC;

Read the name column from first to last. It should be Aaron, Amy, Brian, John, Mark. With DESC, it should be Mark, John, Brian, Amy, Aaron.

For a numeric sort:

SELECT name, surname, year
FROM testtb
ORDER BY year ASC;

Earlier years should appear before later years. If the output does not match the requested order, verify that the query actually contains ORDER BY, that the direction is correct, and that the column has the expected data type.

Ties and deterministic output

Suppose several rows have the same value for every sort key in an ORDER BY clause. MySQL is free to return those tied rows in any relative order. That relative order may change between executions.

For stable and repeatable output, add a secondary key. Ideally, the final key is unique, such as an id column:

SELECT id, name, surname, year
FROM testtb
ORDER BY name ASC, id ASC;

Rows are first sorted by name. Rows with the same name are then sorted by the unique id. Because no two rows have the same complete set of sort-key values, the result has a deterministic order.

Important: A secondary key is used only for ties in the earlier key. If all names are different, id will not change their name order.

Troubleshooting ORDER BY

Rows are not in the expected order

Cause: The query has no ORDER BY, so SQL does not promise a result sequence.

Fix: Add the desired sort column and direction:

SELECT * FROM testtb ORDER BY name ASC;

Descending order is expected, but the output is ascending

Cause: Direction is omitted, and omitted direction means ASC.

Fix: Add DESC to the relevant key:

SELECT * FROM testtb ORDER BY name DESC;

The second sort column seems to have no effect

Cause: The second key is applied only when rows tie on the first key.

Fix: Test with repeated values in the first key, then observe how the second key orders each tied group.

Duplicate values change relative position

Cause: The query does not specify a complete tie-breaker.

Fix: Add a secondary or unique key:

SELECT *
FROM testtb
ORDER BY name ASC, id ASC;

There is a syntax error near ORDER BY

Cause: The clause may be in the wrong position, or multiple keys may not be separated correctly.

Fix: Use the clause sequence SELECT ... FROM ... WHERE ... ORDER BY .... Put ORDER BY after FROM and filtering clauses, and separate sort keys with commas.

Exam-relevant notes

Without ORDER BY, result order is not guaranteed.
ASC is the default direction.
DESC means decreasing order.
Multiple sort keys are comma-separated.
The first sort key has highest precedence.
A later key is used only to resolve ties in earlier keys.
Add a unique secondary key when repeatable ordering is required.