MySQL ORDER BY Keyword

ORDER BY in MySQL is used for arranging data in ascending or descending order. It will sort the result set based on some columns either in ascending or descending order. ORDER BY can be used with date-type columns, columns having null values, etc.

Syntax

Following are the parameters used by MYSQL, while implementing ORDER BY in MySQL.

• column1, column2, ... - These are the names of the columns from which we get the data. The number of columns can be one or more than one.
• table_name - The data we want to retrieve is present in this particular table.
• expression - Data can be sorted using a column or other value such as function, date, or many more.
• ASC - This is an optional parameter that allows the data to be sorted in ascending order while the data is sorted in ascending order by default. It means in MYSQL, the result of the ORDER BY ASC command is the same as the ORDER BY in MySQL.
• DESC – This is an optional parameter that allows the data to be sorted in descending order.

What does ORDER BY do in MySQL?

For sorting the record in ascending or descending order, we use the ORDER BY command. The data will be sorted by default in ascending order when we use ORDER BY in MySQL. But for arranging the data in descending order, we have to implement the DESC keyword with the order by in MySQL.

Examples

First of all, we will create a table with the name EMPLOYEE having EMPLOYEE_ID, EMPLOYEE_NAME, DEPT_ID, and SALARY as an attribute. The query for creating a tale is given below:

Now we will insert some data into the table. The query for inserting the data is given below:

A Simple Example of ORDER BY

We will start with the simple basic example of order by. Here we are using order by for displaying the name of the employee in alphabetically sorted order. Query:

Output:

ORDER BY with ASC or DESC attributes

We can use ASC or DESC attributes with the order by clause. If we use the ASC clause then it will display the same result as without ASC|DESC attributes as order by clause by default sort in the ascending.

Order by clause with ASC attributes

Here we are using order by clause with the asc attributes and we are sorting the result in the ascending of EMPLOYEE_NAME.

Query:-

Output:

Order by clause with DESC attributes

Here we are using order by clause with the desc attributes and we are sorting the result in the descending of EMPLOYEE_NAME.

Query:

Output:

ORDER BY with Multiple Columns

In case of multiple columns with the order by clause, it will first sort the data based on the first column and if there is similar data in the first column then it will perform sorting based on the second column. Let us take an example to fetch the data from the employee table and here we are using order by clause with the salary and dept_id column. So it will sort the data in the ascending order of the salary and if two or more rows have the same salary it will sort those rows in the ascending order of the dept_id.

Query :

Output:

ORDER BY ASC and DESC in the Same Query

Syntax

Firstly sorting will be done based on the first column and its ordering and if there are similar data in the first column written with the order by clause then it will sort those rows based on the ordering given by the second column. Let us take an example to fetch the data from the employee table and here we are using order by clause with the salary(with asc attribute) and dept_id(with desc attribute) column. So it will sort the data in the ascending order of the salary and if two or more rows have the same salary it will sort those rows in the descending order of the dept_id. Query:

Output:

ORDER BY Random Function

If the record is required to be arranged in any random order, then we can use the random() function with the order by.

Query:

Output:

ORDER BY Date

We can also sort the data based on the attribute which contains the date and time. For that, we simply need to write that attribute with the order by.

ORDER BY and LIMIT

If we want to LIMIT the result set of the query containing order by then we can also use the LIMIT in the query. If the LIMIT is used in the query containing the order by then the order by first of all sorts the result, then the number of records to be displayed is restricted according to the LIMIT.
Suppose we want to display the record of the top 5 salaried employees. Then we need to use the order by with the salary column with desc to sort the salary in decreasing order then we use limit 5 to display the top 5 records from the sorted data.

Query:

Output:

ORDER BY and NULL

Sometimes the data fetched by the select statement may contain null values, order by in MySQL also sorts the data which contains null values. The null values are considered smaller than any non-null value in the SQL. So if we use ascending order with the order by then all the values appear at the top. If we want that all the null values appear in the last of the result set then we need to use desc with the order by.

Case Insensitive ORDER BY

Order by in SQL performs case-sensitive sorting of the data. So if the two rows have the same value but are in different cases(lowercase or uppercase), it may affect the result sometimes. So to solve this problem we can modify the query by converting all the data in the lower or upper case. In the query given below, we are sorting the data by converting all the EMPLOYEE_NAME in the lower case by using the lower() function.

Query:

Output:

ORDER BY Length of Characters

It is also possible to sort the data according to the length of the result string. The ORDER BY command can also be implemented using the length of the string. For this, you need to add the LENGTH() function to the clause and create the query in such a way it can arrange the data in ascending or descending order by using the length of the string. Suppose we want to sort the result set in the ascending order of length of the EMPLOYEE_NAME, then we have to write LENGTH(EMPLOYEE_NAME) with the order by clause.

Query:

Output:

ORDER BY Arithmetic Operators

ORDER BY command can also be implemented using arithmetic operators. In this scenario, data will be sorted in the order of the result of the arithmetic operation. Suppose we want to fetch the data in the increasing order of the multiplication of the DEPT_ID*SALARY, then have to write order by clause with the DEPT_ID*SALARY.

Output:

ORDER BY Using a Custom List

Sometimes it is required to sort data in a custom order, such as if we want some data to appear at the top of the result set, then we can also define a custom sorting order for the result set. Suppose we want the EMPLOYEE_NAME ROHAN, KARAN will appear at the top of the result set and then all other EMPLOYEE_NAME will be sorted in alphabetically ascending order then we can write the following query.

Query:

Output:

ORDER BY with GROUP BY and Aggregate Functions

Order by and group by both work on the data fetched by the select query. But they perform different tasks. Order by in MySQL is used for sorting the data based on one or multiple columns. But group by is used to arrange the data in the form of groups which can be used with the aggregate functions(sum(), min(), max(), avg(), count()). In the query, in which group by and order by are used simultaneously then group by will group the data based on the same value and then order by arranging those grouped data in descending or ascending order. Here we are fetching the sum of salaries of all the employees of every department sorted in the increasing of the sum of salary. For that, we need to use the sum() function and for fetching the sum of all employees of every department, we will use group by DEPT_ID, and then for sorting it in the order of the sum of salary, we will use order by sum(salary).

Query:

Output: