MySQL ORDER BY Clause

The ORDER BY clause is used to sort the record-set in ascending or descending order.

Syntax for using ORDER BY clause

This is how the ORDER BY clause is used in MySQL:

SELECT col1, col2, col3... From table_name

[WHERE condition]

[GROUP BY Col(s)]

[HAVING condition]

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]....

A few points to notice about MySQL ORDER BY syntax:

• You may use other statements like WHERE clause, GROUP BY, HAVING, etc. when using the ORDER BY clause.
• If using above mentioned clauses then ORDER BY is used as the last statement.

An example of ORDER BY in ascending order

In this example, a simple SELECT statement is used to retrieve all the records from the demo employees table (tst_employess).

The record set is sorted in ascending order for the single column i.e. employee names. See the query and result:

Query:

SELECT *

FROM tst_employess

ORDER BY employee_name

The Result:

You can see the records are sorted alphabetically for the employee name column. Also, I did not specify any keyword for sorting (ASC or DESC), as such, the default is ascending so results are ordered alphabetically.

The example of sorting results in descending order

In the following query, I only added the DESC keyword for sorting the results in descending order for the employee name column.

Query:

SELECT *

FROM tst_employess

ORDER BY employee_name DESC

Result:

It can be seen that the records are sorted by employee names from Z to A.

The ORDER BY clause with two columns query

As mentioned earlier, you may also use two or more columns with the ORDER BY clause. The sorting order can be set separately for each column.

In the example query below, we have sorted the results on two columns.

The first one is again the employee name and the other is the employee_joining_date. First, have a look and I will explain how it works:

The ORDER BY query:

SELECT *

FROM tst_employess

ORDER BY employee_name ASC , employee_join_date DESC

Result:

The records are first sorted by employee names in ascending order.

If two records match for the employee name then the join date column is checked.

As I specified DESC, the employee who joined on a later date came first.

An example of using a numeric column

The example below shows using a numeric column in the ORDER BY clause. The records of employees are sorted from lowest to highest salary.

The query:

SELECT employee_name AS  "Employee", employee_salary AS  "Salary", employee_age AS  "Age"

FROM tst_employess

ORDER BY employee_salary

The recordset:

That is, if ascending order is used, it sorts from 0-10.

Sorting results by date column only

This example sorts the result by the date column. As DESC keyword is given, employees who joined the latest will come first:

Query:

SELECT employee_name AS  "Employee", employee_join_date AS  "Join Date", employee_age AS  "Age"

FROM tst_employess

ORDER BY employee_join_date DESC

The result-set:

You can see that the last joined employee record is 2017 which came first than the older dates.

Using the Where clause with ORDER BY example

In the following query, the result-set is filtered by using the WHERE clause. A condition is set in the WHERE clause to retrieve only those records with a salary greater than or equal to 4000.

The ORDER BY clause sorts the results based on employee names in ASC order:

SELECT *

FROM tst_employess

WHERE employee_salary >=4000

ORDER BY employee_name

The result-set:

An example of GROUP BY with ORDER BY clause

In this example, I used another table that stores salaries paid to each employee every month. The query uses GROUP BY clause to return record-set of employees with total salaries paid. The ORDER BY clause is used to sort the records from the highest to the lowest total.

The query:

SELECT employee_name, SUM( employee_salary_paid ) AS  "Total Paid"

FROM tst_emp_paid_sal

GROUP BY employee_name

ORDER BY SUM( employee_salary_paid ) DESC

The record-set: