MySQL ORDER BY

Last Updated: 26 July, 2023

In MySQL, the ORDER BY clause is used to sort the result set of a query based on one or more columns. It allows you to specify the ordering of the rows returned by the query.

The basic syntax of the ORDER BY clause is as follows:

 SELECT column1, column2, ...
 FROM table_name
 ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

Here's a breakdown of the syntax:

• SELECT specifies the columns we want to retrieve from the table.
• FROM specifies the table from which we want to retrieve data.
• ORDER BY specifies the columns by which we want to sort the result set.
• column1, column2, ... are the columns we want to sort the result set by.
• ASC (ascending) is the default sort order if not specified explicitly. It arranges the data in ascending order.
• DESC (descending) sorts the data in descending order.

Here's an example to illustrate the usage of the ORDER BY clause.

Suppose we have a table called employees with the following structure, and we have to sort the records in ascending or descending order using one or more columns.

Table: employees

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  | 7  | Amar      | Finance    |   27   |    40,000  |   2023-07-19  | 
  +----+-----------+------------+--------+------------+---------------+

Example 1: Sorting by a single column in ascending order

  mysql> SELECT * FROM employees ORDER BY Age ASC;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  7 | Amar   | Finance    |   27 |  40000 | 2023-07-19  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  |  6 | Sanjay | HR         |   31 |  55000 | 2023-01-29  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  +----+--------+------------+------+--------+-------------+
  7 rows in set (0.07 sec)

Here, we are sorting the records in ascending order based on the Age column. ASC is optional; by default, the ORDER BY clause sorts the records in ascending order.

Example 2: Sorting by a single column in descending order

  mysql> SELECT * FROM employees ORDER BY Salary DESC;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | Salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  |  6 | Sanjay | HR         |   31 |  55000 | 2023-01-29  |
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  7 | Amar   | Finance    |   27 |  40000 | 2023-07-19  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  +----+--------+------------+------+--------+-------------+
  7 rows in set (0.00 sec)

We are sorting the records in descending order according to the Salary column.

Example 3: Sorting multiple columns in a table

We can specify multiple columns in the ORDER BY clause to sort the result set based on multiple criteria. The sorting is performed in the order the columns are specified.

  mysql> SELECT * FROM employees ORDER BY Name ASC, Salary DESC;
  +----+--------+------------+------+--------+-------------+
  | ID | Name   | Department | Age  | salary | JoiningDate |
  +----+--------+------------+------+--------+-------------+
  |  7 | Amar   | Finance    |   27 |  40000 | 2023-07-19  |
  |  4 | Hina   | Marketing  |   38 |  72000 | 2018-08-16  |
  |  5 | Jyanti | Finance    |   29 |  60000 | 2010-02-24  |
  |  2 | Kamal  | HR         |   35 |  38000 | 2021-01-23  |
  |  1 | Rajesh | Sales      |   43 |  54000 | 2023-06-11  |
  |  6 | Sanjay | HR         |   31 |  55000 | 2023-01-29  |
  |  3 | Vimal  | Sales      |   52 |  68000 | 2022-04-08  |
  +----+--------+------------+------+--------+-------------+
  7 rows in set (0.04 sec)

Here, we are retrieving records from the employees table and sorting the result set in ascending order by name and descending order by salary.