MySQL sorting by ORDER BY

We had an employee management system at the time, and management wanted to generate reports such as “highest-paid employees in each department” and “sorted by latest start date.” Initially, I naively wrote the queries without considering the sorting issue. The result… was a mess. Employee names were arranged haphazardly on the page, and salaries showed no discernible pattern. It was then that I truly began to delve into MySQL’s ORDER BY clause and its features.

Based on my experience from multiple projects, here are some personal tips:

1. Use indexes whenever possible. Sorting indexed columns can significantly improve performance. I’ve personally witnessed query times drop from 3 seconds to under 50 milliseconds with proper indexing.
2. Limit the number of columns in your ORDER BY clause. Avoid using excessively large text fields unless absolutely necessary.
3. Use the LIMIT statement. For large datasets, only extract the data you need. My first dashboard query without a LIMIT statement almost crashed my browser.
4. Understand data types. Numbers, dates, and strings behave differently; NULL values ​​can unexpectedly jump to the beginning or end.
5. Use aliases for computed columns. This makes queries more readable and maintainable, especially beneficial for future debugging.

ORDER BY in MySQL is simple in theory but deceptively tricky in practice. It lets you sort query results by one or more columns, either ascending (ASC) or descending (DESC). Without it, MySQL will return rows in an essentially random order, depending on storage and index usage.

Basic syntax looks like this:

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

At first, I assumed ASC and DESC were optional everywhere—but then I ran into a situation where the default ascending order wasn’t giving me what I expected, because I didn’t account for NULL values. That bit caught me off guard.

Simple Sorting Example

We had an employees table for the project:

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
);

I needed a quick way to see who was earning the least so I could check payroll consistency:

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

Debugging Insight: At first, I left out ASC thinking it was redundant, but later I realized adding it made the query self-documenting. When someone else ran it months later, they immediately understood the intention.

Sorting in Descending Order

Management also wanted to see the newest hires first, so I switched to DESC:

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

Here, I learned an interesting quirk: DESC works fine for dates and numbers, but text sorting (like last names) can behave unexpectedly if you have mixed-case entries. That led me to experiment with COLLATE a few times to make alphabetical sorting truly consistent.

Sorting by Multiple Columns

Things got trickier when I needed reports like “highest-paid employee per department.” My first attempt was messy:

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

Here’s what I was thinking as I debugged:

• First, group by department in ascending order.
• Then, within each department, sort by salary descending.

I conducted a test using a test database containing 1200 employees. Initially, the results weren’t quite right—I forgot to add an index to the department table, so MySQL scanned all records, and the query took nearly 3 seconds. After adding a simple composite index (department, salary), the query time dropped to 0.03 seconds. Although it was just a small change, the actual effect was drastically different.

Sorting with Expressions

One day, a manager requested a “projected salary” report, which showed a 10% salary increase for ranking purposes. My first thought was to perform the calculation outside the database, but then I remembered that MySQL can sort by expression:

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

I encountered a small problem here. Without the alias adjusted_salary, I had to repeat this expression in the ORDER BY clause, which made the query harder to read and more error-prone. After renaming it, everything became much clearer.

Combined Filtering and Sorting

Finally, filtering and sorting became a routine task. Here’s an example from our sales department:

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

This feature was a lifesaver when management requested a list of “top sales performers with annual incomes exceeding $50,000.” Initially, I tried running an ORDER BY clause and then filtering in the code. This went terribly wrong. Later, when we combined it with pagination, the queries returned inconsistent pages. Since I moved the filtering operation into the SQL statement, everything works perfectly, and performance has improved.