← Back to Python Course | Chapter 15: Python & MySQL | Lesson 7 of 12

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:

  1. Sorting Ascending (the Default)
  2. Sorting Descending
  3. Sorting by Multiple Columns
  4. Combining ORDER BY with WHERE
  5. Sorting by an Expression or Alias
Syntax
python 
cursor.execute("SELECT * FROM table_name ORDER BY column ASC")  # or DESC

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")  # ascending is the implicit default
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")  # DESC reverses the sort order
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")  # name breaks ties within the same city
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",  # filtered first, then the subset is sorted
    ("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"  # sorts by the computed alias
)
print("Sorted by the computed 'total' alias")
Related Topics

Python MySQL Select With a Filter · Python MySQL Delete Record · Python MySQL Select From Table · Python MySQL Drop Table

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. 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.
  2. Forgetting the DESC keyword and expecting descending order, when ascending is the silent default for ORDER BY.
  3. 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

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.