Summary -

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

ORDER BY multiple columns is used to sort the data at multiple levels from existing table.

ORDER BY multiple columns works with SELECT statement only.

Syntax -

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

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 – Fetch rows by sorting multiple rows in ascending and descending order.

Requirement – Fetch emp_name, designation, salary, dept_id details in ascending order of designation and descending order of salary. The query was as follows –

SELECT emp_name, designation, salary, dept_id FROM employee_details  
ORDER BY designation ASC, salary DESC; 

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

emp_name designation salary dept_id
Employee5 Analyst 20000.00 1000
Employee6 Analyst 18000.00 1000
Employee7 Clerk 15000.00 1000
Employee1 Director 45000.00 1000
Employee2 Director 40000.00 2000
Employee3 Manager 27000.00 1000
Employee4 Manager 25000.00 2000
Employee8 Salesman 14000.00 2000
Employee9 Salesman 13000.00 2000

Scenario – Fetch rows by sorting multiple rows in ascending order.

Requirement – Fetch emp_name, designation, dept_id details in ascending order of designation and dept_id. The query was as follows –

SELECT emp_name,designation,designation,dept_id FROM employee_details 
ORDER BY designation,dept_id ASC; 

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

emp_name designation dept_id
Employee5 Analyst 1000
Employee6 Analyst 1000
Employee7 Clerk 1000
Employee1 Director 1000
Employee2 Director 2000
Employee3 Manager 1000
Employee4 Manager 2000
Employee8 Salesman 2000
Employee9 Salesman 2000

Scenario – Fetch rows by sorting multiple rows in descending order.

Requirement – Fetch emp_name, date_of_hire, salary details of employees whose dept_id is 2000 and descending order of date_of_hire, salary. The query was as follows –

SELECT emp_id, date_of_hire, salary FROM employee_details 
 WHERE dept_id = 2000 ORDER BY date_of_hire, salary DESC; 

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

emp_id date_of_hire salary
004 2019-10-08 25000.00
009 2019-10-08 13000.00
008 2019-09-09 14000.00
002 2019-07-11 40000.00