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:
SELECT * FROM tst_employess ORDER BY employee_name
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.
SELECT * FROM tst_employess ORDER BY employee_name 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
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.
SELECT employee_name AS "Employee", employee_salary AS "Salary", employee_age AS "Age" FROM tst_employess ORDER BY employee_salary
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:
SELECT employee_name AS "Employee", employee_join_date AS "Join Date", employee_age AS "Age" FROM tst_employess ORDER BY employee_join_date DESC
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
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.
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