Summary -

In this topic, we described about the ORDER BY Clause with detailed example.

ORDER BY clause used to sort the data in result-set. The sorting can be either Ascending or descending order. ORDER BY clause sort data in ascending order by default. The keywords are - DESC is used for Descending order and ASC for Ascending order.

Syntax -

SELECT column1, column2, …, columnN
FROM table-name 
[ WHERE condition ]
[ORDER BY column-names ASC|DESC]; 
  • column1, column2, …, columnN – Specifies the name of the column that are used to fetch from table.
  • column-names – Specifies the name of the columns that are used to sort data.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario- Sorting one column data in ascending order.

Requirement – Fetch emp_id, emp_name, salary, dept_id details of employees in department lowest to highest. The query was as follows -

SELECT emp_id, emp_name, salary, dept_id FROM employee_details
ORDER BY dept_id ASC; 

By executing above query, we can get results as shown below –

emp_id emp_name salary dept_id
001 Employee1 45000.00 1000
003 Employee3 27000.00 1000
005 Employee5 20000.00 1000
006 Employee6 18000.00 1000
007 Employee7 15000.00 1000
002 Employee2 40000.00 2000
004 Employee4 25000.00 2000
008 Employee8 14000.00 2000
009 Employee9 13000.00 2000

Scenario- Sorting one column data in descending order.

Requirement – Fetch emp_id, emp_name, salary details of employees in highest salary first. The query was as follows –

SELECT emp_id, emp_name, salary FROM employee_details 
ORDER BY salary DESC; 

By executing above query, we can get results as shown below –

emp_id emp_name salary
001 Employee1 45000.00
002 Employee2 40000.00
003 Employee3 27000.00
004 Employee4 25000.00
005 Employee5 20000.00
006 Employee6 18000.00
007 Employee7 15000.00
008 Employee8 14000.00
009 Employee9 13000.00

Scenario- Sorting data by default.

Requirement – Fetch emp_id, emp_name, date_of_hire details of employees in ascending order of date_of_hire. The query was as follows –

SELECT emp_id, emp_name, date_of_hire
FROM employee_details ORDER BY date_of_hire;   

By executing above query, we can get emp_id, emp_name, date_of_hire details of employees from employee_details table in date_of_hire ascending order by default. The output was as follows-

emp_id emp_name date_of_hire
001 Employee1 2019-07-11
002 Employee2 2019-07-11
003 Employee3 2019-07-11
005 Employee5 2019-07-11
007 Employee7 2019-07-11
008 Employee8 2019-09-09
004 Employee4 2019-10-08
006 Employee6 2019-10-08
009 Employee9 2019-10-08

Scenario- Sorting data with where clause.

Requirement – Fetching employees in dept_id 1000 and highest salary first. The query was as follows –

SELECT * FROM employee_details WHERE dept_id = 1000 ORDER BY 
salary DESC; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

Scenario- Sorting one column in ascending order and another one in descending order.

Requirement – Fetching employee details in date of joining from latest to older and employee name ascending. The query was as follows –

 SELECT * FROM employee_details WHERE dept_id = 1000
ORDER BY date_of_hire DESC, emp_name ASC; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000