What is MySQL ORDER BY clause?

The ORDER BY clause is used to sort the record-set in ascending or descending order. A few points about the MySQL ORDER BY clause:

  • The default sorting is ascending. So, if you do not specify, the record-set is sorted in ascending order.
  • For sorting the records in descending order, use the DESC keyword.
  • For explicitly sorting the record-set in ascending order, use the ASC keyword.
  • The ORDER BY clause is used as the last statement. For example, if your SELECT statement contains WHERE clause only then ORDER BY comes after the WHERE clause.
  • You may use one or more columns in the ORDER BY clause
  • If using two or more columns, the ascending or descending order can be specified for each separately.
  • The usage is shown in the examples in the coming section.

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. as 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 recordset 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:

MySQL ORDER BY

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:

MySQL ORDER BY desc

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 joining date. First, have a look and I will explain how it worked:

The ORDER BY query:

SELECT *

FROM tst_employess

ORDER BY employee_name ASC , employee_join_date DESC

Result:

ORDER BY desc twocols

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, so the employee who joined on the 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:

ORDER BY numeric

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

Sorting results by date column only

This example sorts the result by the date column. As DESC keyword is given so 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:

ORDER BY date

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

Using 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 on the basis of employee names in ASC order:

SELECT *

FROM tst_employess

WHERE employee_salary >=4000

ORDER BY employee_name

The result-set:

ORDER BY where

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 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:

ORDER BY group by

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!