Python MySQL Order By

An ORDER BY clause sorts the rows a SELECT statement returns, either ascending (the default, or explicit with ASC) or descending (with DESC) -- sorting happens on the database server before results are sent to Python, which is typically far more efficient than sorting afterward in Python.

In this page:

Sorting Ascending (the Default)
Sorting Descending
Sorting by Multiple Columns
Combining ORDER BY with WHERE
Sorting by an Expression or Alias

Sorting Ascending (the Default)

cursor.execute("SELECT * FROM customers ORDER BY name") returns customer rows sorted alphabetically by name from A to Z -- ascending order is the implicit default, so ASC does not need to be written explicitly, though it can be for clarity.

Note: Add ORDER BY explicitly any time row order matters for your application, even if the current data happens to look sorted already -- do not rely on incidental insertion order.

Warning: Without any ORDER BY at all, MySQL does not guarantee any specific row order in the results -- it may appear consistent during testing yet change under different conditions.

Example: Sorting Ascending (the Default)

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers ORDER BY name")
print("Sorted A to Z by default")

Sorting Descending

Appending DESC after the column name in ORDER BY reverses the sort into descending order -- ORDER BY name DESC sorts alphabetically from Z to A, and ORDER BY price DESC would show the most expensive items first.

Note: Use DESC whenever showing "most recent," "highest," or "newest first" style results, such as a list of orders sorted by date.

Warning: DESC applies individually to the column it directly follows -- in a multi-column ORDER BY, each column needs its own ASC/DESC if they should sort differently.

Example: Sorting Descending

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers ORDER BY name DESC")
print("Sorted Z to A")

Sorting by Multiple Columns

ORDER BY column1, column2 sorts primarily by column1, and for any rows that share the same column1 value, sorts those tied rows by column2 as a tie-breaker -- exactly like sorting a spreadsheet by one column, then a secondary column.

Note: Use multi-column sorting whenever the primary sort key alone would leave ties in an arbitrary order, such as sorting by last name, then by first name for people who share a surname.

Warning: Each column in a multi-column ORDER BY can have its own independent ASC or DESC direction -- they do not have to all sort the same way.

Example: Sorting by Multiple Columns

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers ORDER BY city, name")
print("Sorted by city, then name breaks ties")

Combining ORDER BY with WHERE

ORDER BY can be combined with a WHERE clause in the same query -- MySQL applies the WHERE filter first to narrow down which rows qualify, then sorts only that filtered subset, which is more efficient than filtering afterward in Python.

Note: Always filter with WHERE at the database level rather than fetching everything and filtering in a Python loop -- letting the database do both filtering and sorting minimizes the data transferred over the network.

Warning: The WHERE clause must come before ORDER BY in the SQL statement -- reversing their order is a syntax error.

Example: Combining ORDER BY with WHERE

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT * FROM customers WHERE city = %s ORDER BY name",
    ("Austin",),
)
print("Filtered first, then sorted")

Sorting by an Expression or Alias

ORDER BY is not limited to plain column names -- it can also sort by a computed expression, or by the alias given to a computed column in the SELECT list, letting you sort by values that do not exist as stored columns at all.

Note: Give a computed column a clear alias with AS in the SELECT list, then reference that same alias in ORDER BY, for cleaner and more readable SQL.

Warning: Referencing a computed expression's alias in ORDER BY works in MySQL, but is not universally portable to every SQL database -- worth keeping in mind if the code might need to support other databases later.

Example: Sorting by an Expression or Alias

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT name, price * quantity AS total FROM orders ORDER BY total DESC"
)
print("Sorted by the computed 'total' alias")

Common Mistakes

Assuming SELECT results come back in a specific, predictable order by default -- without an explicit ORDER BY, MySQL does not guarantee any particular row ordering.
Forgetting the DESC keyword and expecting descending order, when ascending is the silent default for ORDER BY.
Trying to sort by a column name that was not included in the accompanying SELECT column list when using SELECT with specific named columns rather than SELECT *.

Chapter Summary

ORDER BY columnName sorts the query's results by that column, ascending by default.
ORDER BY columnName DESC reverses the sort into descending order.
Multiple columns can be listed in ORDER BY, sorting by the first column and using later columns as tie-breakers.

Browser Support

ORDER BY is standard SQL syntax, supported identically across every MySQL and mysql-connector-python version.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Select With a Filter Next Lesson → Python MySQL Delete Record

Python MySQL Order By

Sorting Ascending (the Default)

Sorting Descending

Sorting by Multiple Columns

Combining ORDER BY with WHERE

Sorting by an Expression or Alias

Chapter Quiz — Complete all 12 topics to unlock

Login to run this code