Sort Query Results with ORDER BY in MySQL

Learn how to sort MySQL SELECT results with ORDER BY, including ASC, DESC, multiple sort keys, aliases, NULL values, LIMIT, and performance.

A SELECT statement retrieves rows and columns from a table. Unless you specify an order, MySQL does not promise the sequence in which those rows will be returned. Use the ORDER BY clause whenever the order of a result matters.

Why result order is not guaranteed

A query such as SELECT * FROM testtb; may appear to return rows in insertion order or in the same order each time. That behavior is only an observation of the current execution and storage conditions. It is not a rule that your application can rely on.

Indexes, query plans, storage changes, parallel work, and other implementation details can change the sequence. If a report, page, export, or application needs a particular order, write that order explicitly with ORDER BY.

What ORDER BY does

ORDER BY specifies the sequence of rows in a query result. It sorts the rows returned by the query; it does not permanently rearrange or change the data stored in the table.

In a query containing other clauses, ORDER BY normally appears after FROM, WHERE, GROUP BY, and HAVING. LIMIT, when used, follows ORDER BY.

SELECT column_list
FROM table_name
WHERE condition
GROUP BY grouping_expression
HAVING group_condition
ORDER BY sort_expression ASC
LIMIT row_count;

Not every query uses every clause. For example, a simple query can use only SELECT, FROM, and ORDER BY.

Sort by one column

A sort key is a column or expression that MySQL uses to determine row order. The basic pattern is:

SELECT column_list
FROM table_name
ORDER BY column_name;

For example, sort people alphabetically by their first name:

SELECT *
FROM testtb
ORDER BY name;

Numeric values are sorted by their numeric value when the column has a numeric data type. This query sorts birth years from the smallest year to the largest year:

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

Make sure a column containing numbers is stored using an appropriate numeric type. Sorting number-like text can produce character-based results instead of numeric results.

Ascending and descending order

ASC means ascending order. It means low-to-high for numbers and generally alphabetical or character order for text. Ascending is the default, so these two queries are equivalent:

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

DESC means descending order. It reverses the requested ordering:

SELECT *
FROM testtb
ORDER BY name DESC;

For a year column, descending order puts the largest year first:

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

Pattern                                      Meaning
SELECT ... ORDER BY column;                  One-column ascending sort
SELECT ... ORDER BY column ASC;              Explicit ascending sort
SELECT ... ORDER BY column DESC;             Descending sort
SELECT ... ORDER BY a ASC, b DESC;           Mixed directions

Sort by multiple columns

You can provide several comma-separated sort expressions. The first expression is the primary sort key. A later expression is a secondary sort key, used only when earlier keys have equal values.

This example sorts by surname alphabetically. People with the same surname are then sorted by birth year, with the newest year first:

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

Each sort key can have its own direction. The following examples show the evaluation order:

First sort key       Second sort key       Tie handling
surname ASC          name ASC              Same surname uses name
category ASC         price DESC             Same category uses high price first
last_name ASC        id ASC                Same last name uses unique id

For example, a category is the primary key and price is the secondary key:

SELECT product_name, category, price
FROM products
ORDER BY category ASC, price DESC;

Column names, aliases, and expressions

A selected column can also be used as a sort key:

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

You can usually sort by an alias assigned in the SELECT list. An alias is an alternate name for a selected column or expression:

SELECT name, price * quantity AS total
FROM order_items
ORDER BY total DESC;

You can also sort directly by an expression. This example places text in case-normalized order:

SELECT name
FROM testtb
ORDER BY LOWER(name) ASC;

A calculated total can be sorted without defining an alias:

SELECT product_name, price, quantity
FROM order_items
ORDER BY price * quantity DESC;

Prefer meaningful column names and aliases. MySQL also permits ordinal positions such as ORDER BY 1, which means “sort by the first selected expression,” but this is fragile: changing the order of the SELECT list can silently change the sort.

Make tied ordering deterministic

If two rows have the same value for every listed sort key, ORDER BY does not define which of those rows comes first. Their relative order can vary between executions.

When a repeatable sequence is required, add a unique key as the final sort criterion. In this example, id resolves rows that have the same last and first names:

SELECT id, first_name, last_name
FROM customers
ORDER BY last_name ASC, first_name ASC, id ASC;

A primary key is a common final key because it uniquely identifies each row. This is especially important for pagination, exports, tests, and user interfaces.

NULL values and text sorting

NULL represents a missing or unknown SQL value. It is not the same as zero, an empty string, or a string containing the word “NULL.” MySQL has defined ordering behavior for NULL values. In the usual ascending ordering, NULL values sort before non-NULL values; in descending ordering, they sort after non-NULL values.

If that placement does not match the desired presentation, use an expression to assign NULL rows an explicit priority:

SELECT name, score
FROM results
ORDER BY score IS NULL ASC, score DESC;

Here, non-NULL scores are placed first, and the scores are then sorted from highest to lowest.

Text ordering depends on the collation. A collation is the set of rules MySQL uses to compare and sort character data. Case sensitivity, accent handling, and language-specific comparison rules can affect alphabetical order.

If a query needs a particular comparison rule, an expression can specify a suitable collation available in your MySQL installation:

SELECT name
FROM people
ORDER BY name COLLATE utf8mb4_unicode_ci;

Check the column and database definitions before choosing a collation. For specialized requirements, a normalized expression such as LOWER(name) may also be useful, but expressions can affect performance.

Combine ORDER BY with WHERE and LIMIT

WHERE filters rows before the final result is ordered. This query first keeps people born in or after 1990, then sorts those remaining rows by name:

SELECT name, surname, year
FROM testtb
WHERE year >= 1990
ORDER BY name ASC;

LIMIT restricts how many rows are returned after ordering. The normal top-results pattern is ORDER BY ... LIMIT.

To retrieve the five most recent records by year:

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

To retrieve the five lowest values instead, use ascending order:

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

Using LIMIT without the intended ORDER BY does not reliably return the highest, lowest, newest, or oldest rows.

Performance awareness

Sorting can be costly when MySQL must examine and arrange many candidate rows, especially for a large table or a complex expression. Filtering with an appropriate WHERE condition and selecting only needed columns can reduce the work.

An index is a database structure that may help MySQL find rows efficiently. An index can sometimes support both filtering and ordering when its column sequence matches a common query pattern. However, not every ORDER BY uses an index. MySQL may still need to sort the result.

For production queries, inspect the execution plan with EXPLAIN and evaluate indexes based on real query patterns. Avoid adding indexes indiscriminately: indexes consume storage and can increase the cost of inserts and updates.

EXPLAIN
SELECT name, surname, year
FROM testtb
WHERE year >= 1990
ORDER BY year DESC
LIMIT 5;

Troubleshooting ORDER BY

Rows look ordered without ORDER BY

The current storage or execution behavior happens to return rows in a familiar sequence. Add an explicit ORDER BY whenever the result order matters.

Rows with equal values change position

The listed sort key contains duplicates. Add secondary keys, ending with a unique identifier if a fully stable sequence is needed.

The wrong top five rows are returned

LIMIT may have been used without the intended sort, or the direction may be reversed. Put ORDER BY before LIMIT and verify whether the query needs ASC or DESC.

Text uses unexpected case or accent order

The active collation controls character comparison. Inspect the column or expression collation and choose an appropriate collation or normalized sort expression when necessary.

An ordered query is slow

MySQL may be sorting many candidate rows or may not have a suitable index. Filter appropriately, select only necessary columns, examine the execution plan, and assess indexes that match common filter-and-sort patterns.

Key points

A SELECT without ORDER BY does not promise a particular row sequence.
ORDER BY sorts the query result and does not change stored table data.
ASC is the default direction; DESC reverses the order.
Multiple sort keys are evaluated from left to right, and later keys resolve earlier ties.
Add a unique final key when equal sort values must have a repeatable order.
NULL placement and text ordering depend on MySQL behavior and collation rules.
Use ORDER BY before LIMIT when retrieving top or bottom rows.
Use EXPLAIN to investigate the performance of important ordered queries.

Exam note: Remember the common clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, then LIMIT. Also remember that an omitted sort direction means ASC.