Python MySQL ORDER BY – Sort Query Results

This chapter shows how to sort MySQL query results in Python using the ORDER BY clause. You will learn how to sort ascending and descending, sort by multiple columns, combine ORDER BY with WHERE and LIMIT, and apply Python best practices such as parameterized queries and proper error handling.

What ORDER BY Does

The ORDER BY clause tells MySQL to return rows in a specified sequence rather than in storage order (which is unpredictable). You can sort by one or more columns, in ascending (ASC) or descending (DESC) order.

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

Key rules:

ASC (ascending, A → Z, 0 → 9) is the default — you can omit it.
DESC (descending, Z → A, 9 → 0) must be stated explicitly.
Multiple columns are separated by commas; MySQL sorts by the first column first, then uses the next column to break ties.
NULL values sort before non-NULL values in ASC order and after them in DESC order.

Prerequisites

You need the mysql-connector-python package. Install it once with pip:

pip install mysql-connector-python

All examples below assume a customers table with at least id, name, and address columns. See Python MySQL – Create Table if you need to create it first.

Sort Ascending (Default)

Ascending order is the default, so ASC is optional. The following query returns all customers sorted alphabetically by name:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    mycursor.execute("SELECT * FROM customers ORDER BY name")

    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Database error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

The try / except / finally block ensures the connection is always closed, even when an error occurs.

Sort Descending

Add DESC after the column name to reverse the order. This returns customers sorted Z → A:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    mycursor.execute("SELECT * FROM customers ORDER BY name DESC")

    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Database error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Sort by Multiple Columns

When the first sort column has ties, MySQL uses the second column to break them. This is useful when, for example, you want customers sorted by city first, then by name within each city:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Sort by address (city) ascending, then by name ascending within each city
    mycursor.execute("SELECT * FROM customers ORDER BY address ASC, name ASC")

    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Database error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

Each column in a multi-column sort can have its own direction:

-- Most recent orders first; within the same date, highest total first
SELECT * FROM orders ORDER BY order_date DESC, total_amount DESC;

Combine ORDER BY with WHERE

ORDER BY is applied after WHERE filters the rows. Use parameterized queries (the %s placeholder) to safely pass values — never format user input directly into the SQL string.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Find all customers in London, sorted by name
    sql = "SELECT * FROM customers WHERE address = %s ORDER BY name ASC"
    val = ("London",)
    mycursor.execute(sql, val)

    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Database error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

The %s placeholder is replaced by MySQL (not Python string formatting), which prevents SQL injection regardless of what the value contains.

Combine ORDER BY with LIMIT

Use LIMIT together with ORDER BY to implement patterns such as "top N records" or pagination. The sort order must come before LIMIT in the SQL statement.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # The 5 customers whose names come first alphabetically
    mycursor.execute("SELECT * FROM customers ORDER BY name ASC LIMIT 5")

    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Database error: {e}")
finally:
    if mycursor:
        mycursor.close()
    if mydb.is_connected():
        mydb.close()

For pagination (skipping earlier pages), add OFFSET:

-- Page 2: rows 6–10
SELECT * FROM customers ORDER BY name ASC LIMIT 5 OFFSET 5;

See Python MySQL – Limit for a full walkthrough of LIMIT and OFFSET.

Fetch One Row vs All Rows

After executing an ORDER BY query you can retrieve results in different ways:

Method	Returns	Use when
`fetchall()`	List of all matching tuples	Result set is small enough to hold in memory
`fetchone()`	First row as a tuple (or `None`)	You only need the top record
`fetchmany(n)`	List of next `n` rows	Processing a large result set in chunks

Example — fetch only the alphabetically first customer:

mycursor.execute("SELECT * FROM customers ORDER BY name ASC")
top = mycursor.fetchone()
print(top)  # e.g. (1, 'Alice', 'London')

Common Gotchas

Sorting strings as numbers. If a column stores numbers as VARCHAR, MySQL sorts them lexicographically ('10' comes before '9'). Cast the column when you need numeric order:

SELECT * FROM products ORDER BY CAST(price AS DECIMAL(10,2)) ASC;

Sorting without ORDER BY is non-deterministic. MySQL does not guarantee row order unless you specify ORDER BY. Relying on insertion order will break unpredictably as the table grows or indexes change.

Column aliases work in ORDER BY. If you define an alias in SELECT, you can use it in ORDER BY:

SELECT name, address AS city FROM customers ORDER BY city ASC;

ORDER BY position. You can reference a column by its position in the SELECT list (e.g., ORDER BY 1), but this is fragile — avoid it in production code because it breaks silently when you reorder columns.

Python MySQL – Get Started — install the connector and connect to MySQL
Python MySQL – Select — read data with SELECT
Python MySQL – Where — filter rows with WHERE
Python MySQL – Limit — restrict result count with LIMIT
Python MySQL – Join — combine data from multiple tables