MySQL ORDER BY: Sorting data in MySQL is essential for organizing query results in a meaningful way. The ORDER BY clause allows you to arrange data in ascending (ASC) or descending (DESC) order based on one or more columns. This tutorial will cover everything you need to know about sorting in MySQL, including practical examples.
Table of Contents
- What is ORDER BY in MySQL?
- ORDER BY Syntax in MySQL
- Sorting in Ascending Order (ASC)
- Sorting in Descending Order (DESC)
- Sorting by Multiple Columns
- Using ORDER BY with NULL Values
- Practical Examples
- Summary
1. What is ORDER BY in MySQL?
MySQL ORDER BY: The MySQL ORDER BY clause is used with the SELECT statement to sort the result set based on a specified column. It can be used to sort data in either ascending (ASC) or descending (DESC) order.
By default, MySQL returns results in the order the records were inserted into the database. Using ORDER BY, you can sort them in a structured way, making data easier to analyze and display.
2. ORDER BY Syntax in MySQL
The basic syntax for sorting query results in MySQL is:
sql
SELECT column_name(s) FROM table_name
[WHERE condition]
ORDER BY column_name [ASC | DESC];
Breakdown of the Syntax:
- SELECT column_name(s): Retrieves data from the table.
- FROM table_name: Specifies the table to fetch data from.
- [WHERE condition]: (Optional) Filters the result set before sorting.
- ORDER BY column_name: Sorts data based on the specified column.
- [ASC | DESC]: Defines whether the sorting is ascending or descending (default is ASC).
3. Sorting in Ascending Order (ASC)
MySQL ORDER BY: The ASC keyword sorts query results from lowest to highest (numbers) or A to Z (strings). If ASC is omitted, MySQL automatically assumes ascending order.
Example: Sort Members by Date of Birth (Oldest to Youngest)
sql
SELECT * FROM members ORDER BY date_of_birth ASC;
Result:
| membership_number | full_names | date_of_birth |
| 2 | Janet Smith Jones | 23-06-1980 |
| 1 | Janet Jones | 21-07-1980 |
| 9 | Howard Wolowitz | 24-08-1981 |
Here, the oldest members appear first in the list.
4. Sorting in Descending Order (DESC)
MySQL ORDER BY: The DESC keyword sorts query results from highest to lowest (numbers) or Z to A (strings).
Example: Sort Members by Date of Birth (Youngest to Oldest)
sql
SELECT * FROM members ORDER BY date_of_birth DESC;
Result:
| membership_number | full_names | date_of_birth |
| 3 | Robert Phil | 12-07-1989 |
| 4 | Gloria Williams | 14-02-1984 |
| 1 | Janet Jones | 21-07-1980 |
Now, the youngest members appear first.
5. Sorting by Multiple Columns
You can sort query results using multiple columns by separating them with commas.
Example: Sort Members by Gender (ASC) and Date of Birth (DESC)
sql
SELECT * FROM members ORDER BY gender ASC, date_of_birth DESC;
How it Works:
- First, it sorts by gender (A to Z, Female first, Male second).
- Then, within each gender, it sorts by date_of_birth in descending order (youngest first).
6. Using ORDER BY with NULL Values
MySQL treats NULL values as the smallest possible values when sorting in ascending order.
Example: Sort Members by Date of Birth (ASC) with NULL Values
sql
SELECT * FROM members ORDER BY date_of_birth ASC;
How MySQL Sorts NULLs:
- Ascending (ASC): NULL values appear first.
- Descending (DESC): NULL values appear last.
7. Practical Examples of ORDER BY in MySQL
Example 1: Sorting Movie Titles in Alphabetical Order
sql
MySQL ORDER BY: SELECT * FROM movies ORDER BY title ASC;
→ Movies will be listed from A to Z.
Example 2: Sorting Payments in Reverse Chronological Order
sql
SELECT * FROM payments ORDER BY payment_date DESC;
→ Latest payments appear first, helping businesses track recent transactions.
Example 3: Sorting Members by Gender and Name
sql
SELECT * FROM members ORDER BY gender ASC, full_names ASC;
→ This will list all Female members first, followed by Male members, sorted by name.
8. Summary
✅ MySQL ORDER BY: MySQL ORDER BY is used to sort query results in ascending (ASC) or descending (DESC) order.
✅ The default sorting order is ascending (ASC) if no order is specified.
✅ You can sort by multiple columns by separating them with commas.
✅ NULL values appear first in ASC order and last in DESC order.
✅ ORDER BY is useful for listing recent transactions, organizing search results, and displaying data meaningfully.
By mastering MySQL ORDER BY, you can efficiently organize query results to match business needs and user preferences!
Click To Open
👉Tutorial-2: SQL GROUP BY and HAVING CLAUSE
👉Tutorial-3: MYSQL WILDCARD Tutorial
👉Tutorial-4: MYSQL REGEX Tutorial
👉Tutorial-5: Complete Guide to MySQL Functions
👉Tutorial-6: MySQL Aggregate Functions
