Order by Clause in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Order by Clause in MySQL with Examples

In this article, I am going to discuss Order by Clause in MySQL with Examples. Please read our previous article where we discussed the Having clause in MySQL.

What is Order by Clause in MySQL?

The Order by Clause in MySQL is used for sorting the data either in ascending or descending order based on a specified column or list of columns. That means if you want to sort the output or result of a query either in ascending or descending order then you need to use MySQL Order by Clause.

Syntax to use Order by Clause in MySQL:

Following is the syntax to use Order by Clause in MySQL.

Parameters or Arguments used with Order by Clause:

1. Expressions: The columns or calculations that you want to retrieve.
2. Tables: The tables from which you want to retrieve the records. There must be at least one table specified in the FROM clause.
3. WHERE Conditions: It is optional. The conditions that must be met for the records to be selected by the query.
4. ASC: It is optional. If you want to sort the result set in ascending order of the expression then you need to use ASC. 
5. DESC: It is optional. If you want to sort the result set in descending order by expression then you need to the DESC keyword.

Note: By default, Order by Clause in MySQL will sort the data in ascending order. If you want to arrange the data in descending order then you need to use the DESC keyword. The Order by Clause can be applied to any data type column in the table. This clause will arrange the data temporarily but not in the permanent store. The Order by Clause can only be used in Select Statements.

Example to understand Order by Clause in MySQL:

We are going to use the following Employee table to understand the Order by Clause in MySQL with Examples.

Please use the following SQL Script to create the company database and employee table with the required records.

CREATE DATABASE company;
USE company;

CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1011, 'Pramod Panda', 'IT', 45000, 'Male', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1012, 'Preety Tiwary', 'HR', 55000, 'Female', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1013, 'Santosh Dube', 'IT', 52000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1014, 'Manoj Tripathy', 'HR', 85000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1015, 'Mitali Rout', 'Finance', 70000, 'Female', 26, 'Mumbai');

Sorting without using ASC/DESC attribute in MySQL:

It is also possible to use the ORDER BY clause in MySQL without specifying the ASC or DESC keyword. When we have not used ASC or DESC attribute, then by default it will sort the data in ascending order. In the below example, we are using Order By clause on the Name column without specifying the ASC and DESC keywords. In this case, the records will be sorted in ascending order based on the Name column of the employee table.

SELECT * FROM Employee ORDER BY Name;

When you execute the above query, it will give you the following result set which is sorted based on the Name column in ascending order.

The following SQL Query also gives the same output as the previous one. Here, in the below query we explicitly providing the ASC keyword.

SELECT * FROM Employee ORDER BY Name ASC;

Sorting in Descending Order using Order By DESC:

If you want to sort the result set in descending order then you need to use the DESC attribute in your ORDER BY clause. The following SQL Query will return all records sorted by the Name field in descending order.

SELECT * FROM Employee ORDER BY Name DESC;

When you execute the above SQL Query, it will give you the following result set which is sorted based on the Name column in Descending order.

Order by Clause with Where Clause in MySQL:

Let us see an example of how to use Order by Clause with Where Clause in MySQL. In this case, first, the Where clause is executed and the data get filtered. Then on the filtered data, the order by clause is applied. In the below query, first, it filtered the data based on the City column i.e. it fetches all the employees who belong to London and then applied for the Order by clause on the Name column to sort the employees in ascending order.

SELECT * FROM Employee WHERE City = ‘London’ ORDER BY Name ASC;

When you execute the above SQL Query, it will give you the following result set which includes the employees of London city and the employees are sorted in ascending order by name.

Order by Clause with Multiple Columns in MySQL:

It is also possible in MySQL to use Order by Clause with multiple columns. Let us understand this with an example. Our requirement is, first sort the employee by Department in Descending order and then sort the employees by name in ascending order.

In this case, the order by clause going to contain two columns. Department column with DESC keyword and Name column with ASC keyword as shown in the below SQL Query. Here, first, the employees are sorted based on the first column and then the employees are sorted based on the second column. But the point that you need to focus on is the second sorting is performed based on the first sorting i.e. for IT department employees it will sort the employees by name and again for HR department employees, it will sort by name and so on.

SELECT Id, Department, Name, Salary, City, Age, Gender
FROM Employee
ORDER BY Department DESC, Name ASC;

When you execute the above query, you will get the following output.

Sorting Data in MySQL by the Relative Position

We can also use the ORDER BY clause to sort the data by relative position in the result set, where the first field in the result is set to 1. The next field is 2, and so on. In the below example, the result set contains four columns. So, the relative position will be 1 for Name, 2 for Department, 3 for Gender, and 4 for City. Further, if you notice in the order by clause, we have specified 2 which means the following SQL Query will sort the data based on the Department column.

SELECT Name, Department, Gender, City FROM Employee ORDER BY 2 ASC;

Once you execute the above query, you will get the following output.

What happens if the relative position does not exist?

In that case, it will throw an error. If you specify 0, any negative number, or a number that is more than the number of columns in the result set then you will get an error. In the below example, the select statement contains four columns and we are using 5 as the relative position to sort the data and hence when you try to execute the below query, you will get an error.

SELECT Name, Department, Gender, City FROM Employee ORDER BY 5 ASC;

When you try to execute the above query, it will give you the error as Error Code: 1054. Unknown column ‘5’ in ‘order clause’

Note: As the name suggests ORDER BY clause is used to order or sort the data rows in a result set. The ORDER BY clause can be used to sort the data rows by one or more columns. By default, the ORDER BY clause sorts the records in ascending order. ASC keyword is used to sort the orders in ascending order while the DESC keyword is used to sort the data rows in descending order

In the next article, I am going to discuss the LIMIT Clause in MySQL with Examples. Here, in this article, I try to explain the Order by Clause in MySQL with Examples. I hope you enjoy this Order by Clause in the MySQL article. I would like to have your feedback. Please post your feedback, question, or comments about this article.