MySql - Sorting with ORDER BY

Sorting with ORDER BY (Ascending and Descending) in MySQL

The ORDER BY clause in MySQL is used to sort the result set returned by a query. It allows you to control the sequence in which rows are displayed—either in ascending (ASC) or descending (DESC) order. Sorting data makes it easier to read, analyze, and retrieve the most relevant or recent results. This detailed guide explores the use of ORDER BY in different scenarios, including sorting by one or multiple columns, using aliases, functions, and performance considerations.

1. Introduction to ORDER BY

1.1 Syntax of ORDER BY

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

The default sorting order is ascending (ASC), so specifying it is optional.

1.2 Simple Example

SELECT * FROM employees
ORDER BY last_name ASC;

This query returns all employees sorted by their last names in ascending order.

2. Sorting in Ascending Order

2.1 Default ASC Order

SELECT * FROM products
ORDER BY price;

This will sort products by price from lowest to highest (ascending by default).

2.2 Explicit ASC Keyword

SELECT * FROM products
ORDER BY price ASC;

This has the same effect as the query above but makes the ascending intent explicit.

3. Sorting in Descending Order

3.1 Using DESC Keyword

SELECT * FROM orders
ORDER BY order_date DESC;

This query returns the most recent orders first by sorting the order_date column in descending order.

4. Sorting by Multiple Columns

You can sort using multiple columns by separating them with commas. The second column is used for ordering rows with the same value in the first column.

4.1 Example

SELECT * FROM employees
ORDER BY department_id ASC, last_name DESC;

This query sorts employees first by department_id in ascending order and then by last_name in descending order within each department.

5. Sorting Using Column Position

You can use the column number instead of the name (not recommended for readability).

5.1 Example

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

This query sorts results by the third column (salary) in descending order.

6. Sorting with Expressions

6.1 Example

SELECT first_name, last_name, (salary + bonus) AS total_compensation
FROM employees
ORDER BY (salary + bonus) DESC;

You can use expressions such as mathematical calculations directly in the ORDER BY clause.

7. Sorting Using Aliases

If you use an alias in the SELECT clause, you can refer to it in the ORDER BY clause.

SELECT first_name, last_name, (salary * 12) AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

8. Sorting by Date

8.1 Example - Most Recent Records

SELECT * FROM logs
ORDER BY created_at DESC
LIMIT 10;

8.2 Sorting by Year or Month

SELECT * FROM orders
ORDER BY YEAR(order_date), MONTH(order_date);

9. Sorting Strings (Text)

Sorting is done lexicographically (dictionary order). Uppercase letters may come before lowercase depending on collation settings.

SELECT name FROM customers
ORDER BY name ASC;

9.1 Case-Insensitive Sorting

SELECT name FROM customers
ORDER BY LOWER(name);

10. Sorting with NULL Values

By default, MySQL treats NULL values as lower than non-NULLs when sorting ascending and higher in descending order.

10.1 Example

SELECT * FROM employees
ORDER BY manager_id ASC;

10.2 Force NULLS LAST

SELECT * FROM employees
ORDER BY manager_id IS NULL, manager_id;

This sorts NULLs last when sorting in ascending order.

11. Using ORDER BY with LIMIT

Often used for pagination or getting top records.

11.1 Get Top 5 Highest Salaries

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;

11.2 Pagination Example

SELECT * FROM employees
ORDER BY last_name ASC
LIMIT 10 OFFSET 20;

12. ORDER BY with GROUP BY

After grouping, you can order the groups.

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;

13. ORDER BY with JOINs

13.1 Sorting Based on Joined Table

SELECT o.id, c.name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER BY c.name ASC;

14. ORDER BY with DISTINCT

When using DISTINCT, ORDER BY should refer only to selected columns.

SELECT DISTINCT department_id
FROM employees
ORDER BY department_id DESC;

15. ORDER BY and Performance

• ORDER BY can slow down large queries—especially without indexes.
• Use LIMIT to reduce the workload of sorting.
• Sorting on indexed columns improves performance.
• Use covering indexes when possible for sorted queries.

15.1 EXPLAIN Plan

EXPLAIN SELECT * FROM employees
ORDER BY salary DESC;

This helps identify if filesort is used, which is less efficient.

16. Common Use Cases

16.1 Sorting Students by Grades

SELECT * FROM students
ORDER BY grade DESC;

16.2 Sorting Blog Posts by Popularity

SELECT * FROM posts
ORDER BY views DESC, published_date DESC;

16.3 Showing Recent Activity

SELECT * FROM activity_log
ORDER BY timestamp DESC
LIMIT 50;

16.4 Sorting Products by Name

SELECT * FROM products
ORDER BY name ASC;

The ORDER BY clause is a powerful tool in MySQL that allows for precise sorting of query results. Whether you're retrieving the highest-paid employees, sorting products alphabetically, or listing the most recent orders, ORDER BY gives you control over the presentation of your data. Mastering both ascending and descending orders, multi-column sorting, and optimizing performance for large datasets will significantly enhance your ability to work efficiently with MySQL.