The MySQL ORDER BY clause sorts the rows returned by a SELECT query in ascending or descending order. By default, MySQL sorts the rows in ascending order if you do not explicitly specify a sort direction.
The ORDER BY clause allows you to arrange the result set (data) in ascending (ASC) or descending (DESC) order based on one or more columns.
Without the ORDER BY clause, MySQL does not guarantee the order of the returned rows. Therefore, whenever you need the results in a specific sequence, you should use the ORDER BY clause.
Why Use the ORDER BY Clause in MySQL?
Whether you are displaying employee details, customer information, product catalogs, sales reports, or student records, the MySQL ORDER BY clause helps you present query results in a clear, logical, and organized manner. It makes the data easier to read, analyze, and compare.
With the ORDER BY clause, you can sort data in the following ways:
- Ascending order (ASC) – Sorts values from smallest to largest, oldest to newest, or A to Z.
- Descending order (DESC) – Sorts values from largest to smallest, newest to oldest, or Z to A.
- Using multiple columns – Sorts the results by more than one column. When two or more rows have the same value in the first sort column, MySQL uses the next specified column to determine their order. You can specify a different sort direction (ASC or DESC) for each column if needed.
- By column names or column positions – Sorts the results using either the column name or its position in the SELECT list. However, using column names is recommended because it improves query readability and maintainability.
- By expressions – Sorts the results based on calculated values or expressions, such as salary * 12, price – discount, or LENGTH(name).
Basic Syntax of MySQL ORDER BY
The ORDER BY clause is used to sort the rows returned by a SELECT query. You can sort the result set based on one or more columns in ascending (ASC) or descending (DESC) order. The general syntax of MySQL ORDER BY is:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name;
In this syntax:
- SELECT column1, column2, … specifies the columns you want to retrieve from the table.
- FROM table_name specifies the table from which the data is fetched.
- ORDER BY column_name sorts the result set based on the specified column.
If you do not specify a sort direction, MySQL sorts the rows in ascending (ASC) order by default.
Important Note:
The ORDER BY clause is placed after the FROM clause. If the query contains a WHERE, GROUP BY, or HAVING clause, the ORDER BY clause appears after them.
SELECT → FROM → WHERE→ GROUP BY → HAVING → ORDER BY → LIMIT
Full Syntax Template
SELECT column1, column2
FROM table_name
WHERE condition -- 1. Filters raw rows
GROUP BY column1 -- 2. Groups rows
HAVING group_condition -- 3. Filters grouped results
ORDER BY column1 [ASC|DESC] -- 4. Sorts final result set
LIMIT offset, count; -- 5. Restricts number of output rowsIf you put ORDER BY before WHERE, GROUP BY, or HAVING, MySQL will raise a syntax error.
Syntax of ORDER BY with ASC
The ASC keyword sorts the result set in ascending order. This is the default sorting direction, so you can omit ASC if you want the data in ascending order.
SELECT column1, column2
FROM table_name
ORDER BY column_name ASC;
In this syntax:
- The keyword “ASC” stands for “Ascending.”
- It sorts numeric values from smallest to largest.
- It sorts text values in alphabetical order (A to Z) according to the column’s collation.
- It sorts date and time values from earliest to latest.
Syntax of ORDER BY with DESC
The DESC keyword sorts the result set in descending order. The general syntax is:
SELECT column1, column2
FROM table_name
ORDER BY column_name DESC;
In this syntax:
- The keyword “DESC” stands for “Descending.”
- It sorts numeric values from largest to smallest.
- It sorts text values in reverse alphabetical order (Z to A) according to the column’s collation.
- It sorts date and time values from latest to earliest.
Creating a Sample Table
Suppose we have an employees table that stores employee details such as employee ID, name, department, salary, and age.
| emp_id | emp_name | department | salary | age |
|---|---|---|---|---|
| 101 | Rahul | IT | 55000 | 25 |
| 102 | Priya | HR | 45000 | 30 |
| 103 | Amit | IT | 70000 | 28 |
| 104 | Neha | Sales | 50000 | 27 |
| 105 | Vikram | HR | 65000 | 35 |
Sort Records in Ascending Order
The following SQL query sorts the employees by their salary in ascending order.
SELECT *
FROM employees
ORDER BY salary;Output:
| emp_id | emp_name | department | salary | age |
|---|---|---|---|---|
| 102 | Priya | HR | 45000 | 30 |
| 104 | Neha | Sales | 50000 | 27 |
| 101 | Rahul | IT | 55000 | 25 |
| 105 | Vikram | HR | 65000 | 35 |
| 103 | Amit | IT | 70000 | 28 |
In this query:
- The ORDER BY clause sorts the result set using the salary column.
- Since there is no sort direction specified with the ORDER BY clause, MySQL automatically uses ascending (ASC) order. As a result, the employees are displayed from the lowest salary to the highest salary.
- The SELECT * statement returns all columns from the employees table.
- If you specify column names (for example, SELECT emp_id, emp_name, salary), only those columns are returned.
This example demonstrates that the ORDER BY clause uses ascending order by default, so you do not need to write the ASC keyword unless you want to make the query more explicit and easier to understand.
Sort Records Using the ASC Keyword
The following SQL query explicitly specifies the ASC keyword to sort the salaries in ascending order.
SELECT *
FROM employees
ORDER BY salary ASC;
In this query, the ASC keyword explicitly instructs MySQL to sort the rows in ascending order based on the salary column. When you run this query, the output will be the same as shown in the above table.
Sort Records in Descending Order
The following SQL query sorts the employees by their salary in descending order.
SELECT emp_id, emp_name, salary
FROM employees
ORDER BY salary DESC;
Output:
| emp_id | emp_name | salary |
|---|---|---|
| 103 | Amit | 70000 |
| 105 | Vikram | 65000 |
| 101 | Rahul | 55000 |
| 104 | Neha | 50000 |
| 102 | Priya | 45000 |
In this query:
- The ORDER BY clause sorts the result set based on the salary column.
- The DESC keyword tells MySQL to arrange the rows in descending order. As a result, the employee with the highest salary appears first, while the employee with the lowest salary appears last.
Sort Records by Employee Name
The following SQL query sorts the employees by their names in ascending alphabetical order.
SELECT emp_id, emp_name
FROM employees
ORDER BY emp_name;
Output:
| emp_id | emp_name |
|---|---|
| 103 | Amit |
| 104 | Neha |
| 102 | Priya |
| 101 | Rahul |
| 105 | Vikram |
In this query, the ORDER BY clause sorts the result set based on the emp_name column. Since we have not specified any sort direction, MySQL uses ascending (ASC) order by default. It sorts the text values (columns) in alphabetical order (A to Z) according to the column’s collation.
If you want to sort text values (names) in reverse alphabetical order (Z to A), use the DESC keyword.
Sort Records by Multiple Columns
Let’s take an example in which we sort records by multiple columns. The following SQL query sorts the employees first by department in ascending order and then by salary in descending order.
SELECT department, emp_name, salary
FROM employees
ORDER BY department ASC, salary DESC;
Output:
| Department | Employee | Salary |
|---|---|---|
| HR | Vikram | 65000 |
| HR | Priya | 45000 |
| IT | Amit | 70000 |
| IT | Rahul | 55000 |
| Sales | Neha | 50000 |
In this query, the ORDER BY clause contains two columns:
- ORDER BY department ASC, salary DESC;
MySQL sorts the result set in the order in which the columns are specified with the ORDER BY clause.
Step 1: Sort by Department
First, MySQL sorts all rows based on the department column in ascending (ASC) order. Since ASC is specified, the departments are arranged alphabetically:
- HR
- IT
- Sales
At this stage, all employees belonging to the same department are grouped together.
Step 2: Sort by Salary Within Each Department
Next, MySQL examines each department separately. If two or more employees belong to the same department, MySQL uses the salary column as the secondary sort direction. Since the keyword DESC is specified with the salary column, employees within the same department are arranged from the highest salary to the lowest salary.
The Sales department contains only one employee (Neha), so no secondary sort direction is required.
Key Points for Sorting by Multiple Columns
- You can specify multiple columns in the ORDER BY clause by separating them with commas.
- MySQL first sorts the result set by the first specified column.
- If two or more rows have the same value in the first sort column, MySQL uses the next specified column to determine their order.
- You can specify a different sort direction (ASC or DESC) for each column.
- The order in which you specify the columns in the ORDER BY clause is important because it determines the sorting priority.
Using ORDER BY with the WHERE Clause
The following SQL query retrieves employees from the IT department and sorts them by salary in descending order.
SELECT emp_id, emp_name, department, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;
Output:
| emp_id | emp_name | department | salary |
|---|---|---|---|
| 103 | Amit | IT | 70000 |
| 101 | Rahul | IT | 55000 |
In this query, the WHERE clause and the ORDER BY clause work together to filter and sort the data.
First, the WHERE clause filters the rows and returns only the employees whose department is ‘IT’. All employees from other departments, such as HR and Sales, are excluded from the result set.
After the filtering is complete, the ORDER BY clause sorts the filtered rows based on the salary column. Since the DESC keyword is specified with the salary column, MySQL arranges the salaries in descending order from the highest value to the lowest value.
Using ORDER BY with LIMIT
Let’s take an example in which we will use the MySQL ORDER BY clause with LIMIT. The following SQL query sorts the employees by salary in descending order and returns only the top three employees in the result set.
SELECT emp_id, emp_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
Output:
| emp_id | emp_name | salary |
|---|---|---|
| 103 | Amit | 70000 |
| 105 | Vikram | 65000 |
| 101 | Rahul | 55000 |
In this query:
- The ORDER BY clause first sorts all employees by the salary column in descending (DESC) order.
- As a result, the employee with the highest salary appears first, followed by the employee with the next highest salary.
- After the rows have been sorted, the MySQL LIMIT clause restricts the number of rows returned by the query.
- Since LIMIT 3 is specified in the query, MySQL returns only the first three rows from the sorted result set.
- If you omit the ORDER BY clause, LIMIT 3 simply returns the first three rows selected by MySQL, and their order is not guaranteed.
Using ORDER BY with Expressions in MySQL
The ORDER BY clause in MySQL also allows you to sort rows (results) using an expression instead of a single column. MySQL evaluates the expression separately for every row in the result set and then sorts the final output based on the calculated values. Expressions can include arithmetic operators, functions, or combinations of columns. Look at the below SQL query to understand better.
SELECT emp_name, salary
FROM employees
ORDER BY salary * 12 DESC;
Output:
| Employee | Salary (Monthly) |
|---|---|
| Amit | 70000 |
| Vikram | 65000 |
| Rahul | 55000 |
| Neha | 50000 |
| Priya | 45000 |
In this query, the ORDER BY clause sorts the rows using the expression salary * 12.
For each employee, MySQL multiplies the monthly salary by 12 to calculate the annual salary. The calculated value is used only for sorting and is not displayed in the result because it is not included in the SELECT list.
Since the DESC keyword is specified with the salary column, MySQL sorts the employees from the highest annual salary to the lowest annual salary.
Why Does the Output Look the Same?
In this example, the order of the rows is the same as sorting by the monthly salary because multiplying every salary by the same constant value (12) does not change the relative order of the values. Therefore, the ranking remains unchanged.
Key Points of MySQL ORDER BY Clause
- The ORDER BY clause sorts the rows returned by a SELECT statement in ascending or descending order based on or more columns.
- By default, MySQL sorts rows in ascending (ASC) order.
- If you want to sort the result set in descending order, use the DESC keyword.
- When sorting by multiple columns, MySQL sorts by the first column first, then uses the next column if values in the previous column are the same.
- Each column in the ORDER BY clause can have its own sort direction (ASC or DESC).
- You can sort numeric, text, date, and time values.
- You can sort rows using column names, column positions, or expressions.
- You can combine the ORDER BY clause with the WHERE clause to sort only the filtered rows.
- You can combine the ORDER BY clause with the LIMIT clause to retrieve the top or bottom N rows.
- The order of columns listed in the ORDER BY clause determines the sorting priority.
