16 - SQL ORDER BY CLAUSE

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:

SELECT col1, col2, col3,…..colN
FROM table_name
WHERE conditions
ORDER BY col1,col2,col3,…..colN [ASC/DESC];

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.

EMP_ID

EMMP_NAME

AGE

PHONE_NUM

DEPT_ID

SALARY

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

Michal

45

12323434

3

5000

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) 

Example 3:

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

SELECT EMMP_NAME, SALARY FROM EMPLOYEE

WHERE SALARY > 10000

ORDER BY EMMP_NAME;

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

Example 4:

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) 

As you can see the result set is same, because the relative position for EMP name is 2. 

Like us on Facebook