MySQL sorting by ORDER BY

Understanding the ORDER BY Clause

The ORDER BY clause in MySQL is used to sort the result set of a query by one or more columns. By default, it sorts data in ascending order, but you can specify descending order as well. Sorting is essential for reports, analytics, and improving the readability of your data outputs.

Basic Syntax:

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

• column1, column2, ... – Columns to sort by.
• ASC – Sort in ascending order (default).
• DESC – Sort in descending order.

Simple Sorting Example

Assume we have the following employees table:

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

Example 1: Sort by Salary in Ascending Order

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

Step-by-Step Analysis:

• ORDER BY salary ASC sorts the employees from lowest to highest salary.
• ASC is optional since ascending order is the default.
• Sorting improves readability when comparing values like salaries.

Sorting in Descending Order

To display the highest values first, use DESC.

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

Step-by-Step Analysis:

• hire_date DESC shows the most recently hired employees first.
• DESC reverses the default ascending order.

Sorting by Multiple Columns

You can sort results by more than one column to create structured and meaningful outputs.

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

Logic Behind the Query:

• First, employees are grouped by department in ascending order.
• Within each department, employees are sorted by salary in descending order.
• This approach is useful for hierarchical sorting and detailed reporting.

Sorting with Expressions

MySQL allows sorting by expressions, not just columns.

SELECT first_name, last_name, salary, salary * 1.10 AS adjusted_salary
FROM employees
ORDER BY adjusted_salary DESC;

Step-by-Step Analysis:

• salary * 1.10 calculates a 10% increase for sorting purposes.
• AS adjusted_salary gives a temporary name for readability.
• Sorting can be dynamic, based on computed values or functions.

Best Practices for Using ORDER BY

1. Use Indexes When Possible: Sorting on indexed columns improves query performance.
2. Limit Columns for Sorting: Avoid sorting on unnecessary expressions or large text fields.
3. Combine with LIMIT: Use LIMIT to retrieve only top results, especially for large datasets.
4. Understand Data Types: Sorting behaves differently for numeric, text, and date columns.
5. Use Aliases for Clarity: Computed columns should have aliases to improve readability.

Example: Combined Filtering and Sorting

SELECT first_name, last_name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 50000
ORDER BY salary DESC;

Explanation:

• WHERE department = 'Sales' AND salary > 50000 filters relevant employees.
• ORDER BY salary DESC ensures the highest-paid employees appear first.
• Combining filtering and sorting allows precise and organized data retrieval.