The results retrieved from the tables using SELECT statement can be sorted in 3 ways:

1. Using ORDER BY Clause

SQL ORDER BY clause is used to sort the result set that is retrieved using the SELECT statement.

Suppose you run SELECT * FROM table_name twice, there is a possibility that  you may not get same output. This is because once it may give in the order in which rows that were inserted and next run may be in the order of some updates to the rows. Hence it is always good idea to give ORDER BY so as to specify the order of the rows.

Syntax:

Here Conditions are the condition that must be true for the result to  be retrieved.

ASC:  the result is sorted in ascending order by expression. This is optional. If not provided, by default the result would be sorted in ascending order.

DESC: the result is sorted in descending order by expression. This is optional.

Important note: The columns mentioned in ORDER BY should be mentioned in the column list in SELECT statement.

Consider our example of Employee details.

Example 1:

Sort the table based on EMP name in ascending order

SELECT * FROM EMPLOYEE
ORDER BY EMMP_NAME;
+-----------+----------------------+----------+--------------------+---------------+-------------+
| EMP_ID | EMMP_NAME     | AGEG  | PHONE_NUM | DEPT_ID| SALARY    |
+-----------+----------------------+----------+--------------------+---------------+-------------+
|      1 | john          |   35  | 100233023 |       2|  10000    |
|      2 | Linda         |   30  | 100234565 |       1|  15000    |
|      3 | Max           |   40  | 122222344 |       3|  22000    |
|      5 | Michal        |   45  |  12323434 |       3|   5000    |
|      4 | Will          |   40  |  12323424 |       3|  31000    |
+-----------+----------------------+----------+--------------------+---------------+-------------+
5 rows in set (0.05 sec)

Example 2:

Sort on 2 columns, SALARY and EMMP_NAME. Here both columns will be sorted in ascending order.

SELECT * FROM EMPLOYEE
ORDER BY SALARY, EMMP_NAME;
+-----------+----------------------+----------+--------------------+----------------+------------+
| EMP_ID | EMMP_NAME     | AGEG    | PHONE_NUM | DEPT_ID     | SALARY |
+-----------+----------------------+----------+--------------------+----------------+------------+
|      5 | Michal        |   45    |  12323434 |       3     |   5000 |
|      1 | john          |   35    | 100233023 |       2     |  10000 |
|      2 | Linda         |   30    | 100234565 |       1     |  15000 |
|      3 | Max           |   40    | 122222344 |       3     |  22000 |
|      4 | Will          |   40    |  12323424 |       3     |  31000 |
+-----------+----------------------+----------+--------------------+----------------+------------+
5 rows in set (0.00 sec) 

Sort the employees with salary >10000 and display only EMP_NAME and SALARY.

+--------------------+------------+
| EMMP_NAME | SALARY |
+--------------------+------------+
| Linda     |  15000 |
| Max       |  22000 |
| Will      |  31000 |
+--------------------+------------+
3 rows in set (0.03 sec)

Sort the EMP NAME in ascending and SALARY in descending order.

SELECT EMMP_NAME, SALARY FROM EMPLOYEE
ORDER BY EMMP_NAME, SALARY DESC;
+-----------------------------------+------------+
| EMMP_NAME     | SALARY |
+-----------------------------------+------------+
| john          |  10000 |
| Linda         |  15000 |
| Max           |  22000 |
| Michal        |   5000 |
| Will          |  31000 |
+-----------------------------------+------------+
5 rows in set (0.00 sec)

Example 5:

Merge the details of Employee and Department and sort on Employee in descending order and Department names in ascending order.

SELECT EMP_ID,EMMP_NAME,DEPT_NAME
FROM EMPLOYEE, DEPARTMENTS
WHERE EMPLOYEE.DEPT_ID = DEPARTMENTS.DEPT_ID
ORDER BY EMMP_NAME DESC, DEPT_NAME;

+-----------+----------------------+--------------------+
| EMP_ID | EMMP_NAME     | DEPT_NAME  |
+-----------+----------------------+--------------------+
|      4 | Will          | Production |
|      5 | Michal        | Production |
|      3 | Max           | Production |
|      2 | Linda         | Accounts   |
|      1 | john          | HR         |
+-----------+----------------------+--------------------+
5 rows in set (0.00 sec)

Sorting results using RELATIVE Position.

Whenever the columns are mentioned in the SELECT statement then they are given the relative position as 1, 2,3 etc. Like the first column would be 1, second would be 2 and so on.

Hence the above example of sorting EMP name and SALARY can be written as below:

SELECT * FROM EMPLOYEE
ORDER BY SALARY, 2;
+-----------+----------------------+----------+--------------------+----------------+------------+
| EMP_ID | EMMP_NAME     | AGEG    | PHONE_NUM | DEPT_ID     | SALARY |
+-----------+----------------------+----------+--------------------+----------------+------------+
|      5 | Michal        |   45    |  12323434 |       3     |   5000 |
|      1 | john          |   35    | 100233023 |       2     |  10000 |
|      2 | Linda         |   30    | 100234565 |       1     |  15000 |
|      3 | Max           |   40    | 122222344 |       3     |  22000 |
|      4 | Will          |   40    |  12323424 |       3     |  31000 |
+-----------+----------------------+----------+--------------------+----------------+------------+
5 rows in set (0.00 sec)