ORDER BY in MySQL

The ORDER BY clause in MySQL is used to sort the result set based on one or more columns in ascending (ASC) or descending (DESC) order.

1. Basic Syntax

SELECT column1, column2 FROM table_name ORDER BY column1 ASC;  -- Default is ASC (Ascending)

✅ Example Table: employees

2. Sorting in Ascending (ASC) Order

SELECT * FROM employees ORDER BY salary ASC;

✅ Result: (Sorted from lowest to highest salary)

3. Sorting in Descending (DESC) Order

SELECT * FROM employees ORDER BY salary DESC;

✅ Result: (Sorted from highest to lowest salary)

4. Sorting by Multiple Columns

Sort by Department (ASC) and Salary (DESC)

SELECT * FROM employees ORDER BY department ASC, salary DESC;

✅ Result: (Sorted by department first, then salary within the department)

5. Ordering by Column Index

Instead of column names, you can use column index numbers:

SELECT name, department, salary FROM employees ORDER BY 3 DESC;  -- Sort by the third column (salary)

🚀 This works but is NOT recommended (column order may change).

6. Sorting NULL Values

By default, NULL values appear first in ASC order and last in DESC order.

✅ Example: Sorting Employees by Salary (Including NULL)

SELECT * FROM employees ORDER BY salary ASC;

✅ Result: NULL appears first

✅ To move NULL values last in ASC order:

SELECT * FROM employees ORDER BY salary IS NULL, salary ASC;

7. Using ORDER BY with LIMIT

Get the Top 3 Highest Salaries

SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

✅ Returns only the top 3 highest-paid employees.

Get the Second-Highest Salary

SELECT * FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

✅ Skips the first result (highest salary) and fetches the second highest.

8. ORDER BY with CASE (Custom Sorting)

You can use CASE to define custom sorting logic.

SELECT * FROM employees ORDER BY   CASE     WHEN department = 'IT' THEN 1     WHEN department = 'HR' THEN 2     ELSE 3   END;

✅ IT appears first, then HR, then others.

9. ORDER BY with CONCAT (Sorting by Combined Values)

Sort employees by first and last name combined:

SELECT * FROM employees ORDER BY CONCAT(name, department) ASC;

🚀 Useful when sorting by dynamically generated values.

Key Takeaways

✅ ORDER BY sorts data in ASC (default) or DESC order.
✅ You can sort by multiple columns.
✅ NULL values appear first in ASC but can be moved last using IS NULL.
✅ Use LIMIT with ORDER BY to fetch top results.
✅ CASE allows custom sorting logic.