MySQL ORDER BY

By default, MySQL returns rows in the order they were inserted into the table. The ORDER BY clause sorts the result set by one or more columns — either in ascending (A to Z, 1 to 100) or descending (Z to A, 100 to 1) order.

Syntax

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

• ASC — Ascending order (default). Numbers go from lowest to highest; text goes from A to Z.
• DESC — Descending order. Numbers go from highest to lowest; text goes from Z to A.

Sample Table: students

+------------+------------+-----------+-----+
| student_id | first_name | last_name | age |
+------------+------------+-----------+-----+
|          1 | Alice      | Johnson   |  20 |
|          2 | Bob        | Smith     |  22 |
|          3 | Carol      | White     |  19 |
|          4 | David      | Brown     |  21 |
|          5 | Eve        | Taylor    |  22 |
+------------+------------+-----------+-----+

Sorting in Ascending Order

Sort students by age from youngest to oldest:

SELECT first_name, age
FROM students
ORDER BY age ASC;

Output:

+------------+-----+
| first_name | age |
+------------+-----+
| Carol      |  19 |
| Alice      |  20 |
| David      |  21 |
| Bob        |  22 |
| Eve        |  22 |
+------------+-----+

Since ASC is the default, writing ORDER BY age produces the same result.

Sorting in Descending Order

Sort students from oldest to youngest:

SELECT first_name, age
FROM students
ORDER BY age DESC;

Sorting by Text Columns

Sort students alphabetically by last name:

SELECT first_name, last_name
FROM students
ORDER BY last_name ASC;

Output (alphabetical order): Brown, Johnson, Smith, Taylor, White.

Sorting by Multiple Columns

When two rows have the same value in the first sort column, a second column can be used as a tiebreaker:

SELECT first_name, last_name, age
FROM students
ORDER BY age DESC, first_name ASC;

Here, students are sorted by age from highest to lowest. If two students have the same age (Bob and Eve, both 22), they are then sorted alphabetically by first name.

Output:

+------------+-----------+-----+
| first_name | last_name | age |
+------------+-----------+-----+
| Bob        | Smith     |  22 |
| Eve        | Taylor    |  22 |
| David      | Brown     |  21 |
| Alice      | Johnson   |  20 |
| Carol      | White     |  19 |
+------------+-----------+-----+

Using ORDER BY with WHERE

The ORDER BY clause always comes after the WHERE clause:

SELECT first_name, age
FROM students
WHERE age >= 20
ORDER BY age DESC;

This filters first (only students 20 or older), then sorts the results.

Sorting by Column Position

Instead of a column name, a column's position number in the SELECT list can be used:

SELECT first_name, age
FROM students
ORDER BY 2 DESC;

Here, 2 refers to the second column in the SELECT list — age. While this works, using the column name is clearer and preferred.

Sorting with NULL Values

When a column contains NULL values, they are treated as the lowest possible value by default. In ascending order, NULLs appear first; in descending order, they appear last.

Key Points

• ORDER BY column ASC sorts from lowest to highest (default behavior).
• ORDER BY column DESC sorts from highest to lowest.
• Multiple columns can be specified; the second column is used only to break ties.
• The ORDER BY clause comes after WHERE in the query.
• Without ORDER BY, the row order in the result set is not guaranteed.