📊 MySQL ORDER BY Clause – Sort Your Query Results Like a Pro

When working with databases, the way data is displayed matters. MySQL gives us the ORDER BY clause to sort query results based on one or more columns, either in ascending (ASC) or descending (DESC) order.

In this blog, we’ll cover everything you need to know about the ORDER BY clause using real-life examples from employees and departments tables.

🧠 What is the ORDER BY Clause?

The ORDER BY clause is used to sort the result set of a query by one or more columns. You can sort data in:

• Ascending order using ASC (default)
• Descending order using DESC

🧾 Syntax of ORDER BY

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

📋 Sample Tables

We’ll use the following database structure:

✅ departments

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(30),
  location_id INTEGER
);

✅ employees

CREATE TABLE employees (
  employee_id INTEGER,
  first_name VARCHAR(20),
  last_name VARCHAR(25),
  email VARCHAR(25),
  phone_number VARCHAR(20),
  hire_date DATE,
  job_id VARCHAR(10),
  salary INTEGER,
  commission_pct INTEGER,
  manager_id INTEGER,
  department_id INTEGER,
  CONSTRAINT pk_emp PRIMARY KEY (employee_id),
  CONSTRAINT fk_deptno FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

1️⃣ ORDER BY ASC – Ascending Order

This is the default sort order.

✅ Example: List employees sorted by first name (A-Z)

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

🟢 Result: Employees will be listed from A to Z by their first names.

2️⃣ ORDER BY DESC – Descending Order

Use DESC to sort from highest to lowest or Z to A.

✅ Example: List employees by highest to lowest salary

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

🟢 Result: Top earners will be shown first.

3️⃣ Sorting by Multiple Columns

You can sort by more than one column. If the first column has duplicate values, the second one is used.

✅ Example: Sort by department, then salary (highest first)

SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

🟢 Result:

• Employees are grouped by department
• Within each department, highest salary is shown first

4️⃣ ORDER BY with WHERE Clause

Combine filtering and sorting.

✅ Example: Employees in department 2, sorted by last name

SELECT first_name, last_name, department_id
FROM employees
WHERE department_id = 2
ORDER BY last_name ASC;

🟢 Result: HR employees listed alphabetically by last name.

5️⃣ ORDER BY Column Position (Not Recommended)

You can order by column position in the SELECT list (e.g., 1, 2, 3), but this is not recommended for clarity.

✅ Example: Sort by the second column in SELECT

SELECT first_name, salary
FROM employees
ORDER BY 2 DESC;

🟢 Result: Sorted by salary in descending order.

🔴 Warning: Using column position is risky — if your SELECT list changes, your sort might behave unexpectedly.

6️⃣ ORDER BY with Aliased Columns

You can sort by a calculated column or alias.

✅ Example: Sort employees by total income (salary + commission)

SELECT first_name, salary + IFNULL(commission_pct, 0) AS total_income
FROM employees
ORDER BY total_income DESC;

🟢 Result: Shows employees earning the most overall.

7️⃣ ORDER BY with JOIN

Sort data from multiple tables.

✅ Example: Show employee names and department names, sorted by department

SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name ASC;

🟢 Result: Employees sorted alphabetically by department name.

📘 Real-World Use Cases

🔹 Use Case 1: Pagination

When using LIMIT for pagination, combine it with ORDER BY to get consistent results.

SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 10;

➡ Shows 10 most recently hired employees.

🔹 Use Case 2: Sorting Search Results

When filtering with LIKE, you can sort results alphabetically or by popularity.

SELECT * FROM employees
WHERE first_name LIKE 'A%'
ORDER BY last_name;

➡ Shows all employees whose first name starts with A, sorted by last name.

🧠 Summary of ORDER BY Clause

🏁 Final Thoughts

The ORDER BY clause is essential when building reports, dashboards, or any data-driven application. It gives your users meaningful and sorted views of the data.

Combine ORDER BY with WHERE, JOIN, and functions to build powerful queries that answer real business questions.

💡 Tip from Acesoftech Academy: Always include ORDER BY when using LIMIT or pagination — it ensures reliable and repeatable results!