MySQL Order By Clause

This section will see Queries that use the Where clause along with the Order by Clause. The Order by Clause sorts the records in either the Ascending order or by Descending order.
We will use the MySQL Workbench for running the queries. The method of using the command-line is the same.

MySQL Order By clause syntax

The basic syntax of an order by clause is as follows:

SELECT [* | column-names]
FROM table-names
WHERE <conditions>
ORDER BY <column-names>[ASC\DESC];

Sorting in ascending (default) order using Order By in MySQL

By default, records are sorted in ascending order.

SELECT * FROM SALARIES 
WHERE SALARY > 155700
ORDER BY from_date;

Sorting in descending order using Order By in MySQL

To sort records in descending order, we use the DESC keyword.

SELECT * FROM SALARIES 
WHERE SALARY > 155700
ORDER BY from_date desc;

Sorting multiple columns using Order By in MySQL

In the case of multiple columns, MySQL sorts using the first column. If there is a conflict i.e. two rows with the same value for the column, then the second column is considered. There is no limit to using columns. The next example shows the same.

SELECT * FROM EMPLOYEES
WHERE HIRE_DATE IS NOT NULL
ORDER BY BIRTH_DATE DESC, LAST_NAME ASC;

Sorting three columns using Order By in MySQL

In this case, MySQL will sort using the first column selected i.e. Gender. If there is a conflict i.e. two rows with the same values, then MySQL will sort using the second column selected i.e. Birth_Date. IF it happens that there are two records with the same Gender and same Birth_date, then these records will be sorted using the First_name column in ascending order.

SELECT * FROM EMPLOYEES
WHERE HIRE_DATE IS NULL
ORDER BY GENDER ASC, BIRTH_DATE DESC, FIRST_NAME ASC;

Order by using Relative position in MySQL

We can order the elements by using the relative positions of the columns selected in the Select clause. For example, if we have selected 3 columns, we can order by using the 2nd column selected by using the column number 2

SELECT emp_no, title,to_date FROM TITLES WHERE TITLE != 'STAFF'
order by 2 asc;

The Order by clause can be used from the simplest of Queries to the most complex Queries. Order by should not be used inside SubQueries for better optimization.

MySQL Order By Clause