MySQL - Order By Clause

MySQL: Sorting Query Results with ORDER BY

The ORDER BY clause is used to sort the rows in a result set based on one or more columns. By default, results are returned in an arbitrary order, which can be unpredictable. Using ORDER BY is essential for presenting data in a meaningful way, whether it’s an alphabetized list of names, a chronological list of events, or a ranked list of products by sales.

Basic Syntax

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• ASC: Sorts in ascending order (A-Z, 0-9). This is the default, so you can omit it.
• DESC: Sorts in descending order (Z-A, 9-0).

Let’s use a sample employees table for our examples.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, hire_date, salary) VALUES
('Aisha', 'Khan', 'Engineering', '2022-08-15', 95000.00),
('Carlos', 'Garcia', 'Sales', '2021-02-20', 80000.00),
('Mei', 'Lin', 'Engineering', '2021-07-01', 110000.00),
('David', 'Smith', 'Sales', '2022-08-15', 78000.00),
('Zoe', 'Washington', 'Marketing', '2023-01-10', 85000.00),
('Brian', 'O''Connell', 'Engineering', '2023-01-10', 92000.00);

Single Column Sorting

To get a list of employees sorted alphabetically by last name:

SELECT first_name, last_name, salary
FROM employees
ORDER BY last_name; -- ASC is implied

To see who has the highest salary:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

Multi-Column Sorting

You can sort by multiple columns. The result set is sorted by the first column, and then for any rows that have the same value in the first column, they are sorted by the second column, and so on. This is useful for grouping and sub-sorting.

Let’s sort employees by department, and within each department, by their hire date to see seniority.

SELECT department, hire_date, first_name, last_name
FROM employees
ORDER BY department ASC, hire_date ASC;

This query first groups all ‘Engineering’ employees, then ‘Marketing’, then ‘Sales’. Within the ‘Engineering’ group, it orders them by who was hired first.

Advanced Sorting Techniques

Sorting by an Expression

You can sort by the result of a function or expression. For example, to sort names by their length:

SELECT first_name, last_name
FROM employees
ORDER BY LENGTH(first_name) DESC;

Handling NULL Values

By default, MySQL considers NULL values to be lower than any non-NULL value. This means NULLs appear first when sorting ASC and last when sorting DESC. You can change this behavior with a simple trick.

-- Let's add an employee with a NULL salary
INSERT INTO employees (first_name, last_name, department, hire_date, salary)
VALUES ('Unknown', 'Intern', 'Engineering', '2023-06-01', NULL);

-- Sort by salary, but put NULLs last
-- ISNULL(salary) returns 1 if salary is NULL, 0 otherwise.
-- So we sort by that first (0s come before 1s), then by the salary itself.
SELECT first_name, last_name, salary
FROM employees
ORDER BY ISNULL(salary) ASC, salary DESC;

Custom Sort Order with FIELD()

Sometimes you need to sort by a specific, non-alphanumeric order. The FIELD() function is perfect for this. It returns the index of a value in a list.

Let’s sort the employees by a specific departmental hierarchy: Engineering first, then Marketing, then Sales.

SELECT first_name, last_name, department
FROM employees
ORDER BY FIELD(department, 'Engineering', 'Marketing', 'Sales');

Practical Application: Fetching Sorted Data in an Application

Fetching sorted data is a common task in backend applications. Here’s a Python example using the mysql-connector-python library.

Python Example with mysql-connector-python

# Setup: pip install mysql-connector-python python-dotenv
# Create a .env file with your DB credentials

import os
import mysql.connector
from mysql.connector import errorcode
from dotenv import load_dotenv

load_dotenv()

def get_employees_sorted_by(sort_column, sort_order='ASC'):
    """Fetches employees with dynamic but safe sorting."""

    # Whitelist allowed columns for sorting to prevent SQL injection
    allowed_columns = ['first_name', 'last_name', 'hire_date', 'salary']
    if sort_column not in allowed_columns:
        raise ValueError(f'Invalid sort column: {sort_column}')

    # Whitelist sort order
    if sort_order.upper() not in ['ASC', 'DESC']:
        raise ValueError(f'Invalid sort order: {sort_order}')

    try:
        conn = mysql.connector.connect(
            host=os.getenv('DB_HOST'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            database=os.getenv('DB_NAME')
        )
        cursor = conn.cursor(dictionary=True)

        # Safely construct the query
        query = f"SELECT first_name, last_name, salary FROM employees ORDER BY {sort_column} {sort_order}"

        print(f'Executing query: {query}')
        cursor.execute(query)

        for row in cursor.fetchall():
            print(f"{row['first_name']} {row['last_name']} - Salary: {row['salary']}")

    except mysql.connector.Error as err:
        print(f'Error: {err}')
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

# --- Usage ---
print('--- Sorting by salary descending ---')
get_employees_sorted_by('salary', 'DESC')

print('\n--- Sorting by last_name ascending ---')
get_employees_sorted_by('last_name')

Security Warning: Never directly inject user input into an ORDER BY clause. This can lead to SQL injection. Always validate and whitelist the column names and sort directions in your application code, as shown in the Python example.