Summary -

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

ORDER BY DESC statement is used to sort data in result-set in descending order. ORDER BY DESC statement is used in SELECT statement.

Syntax -

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

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 in descending order of single column.

Requirement – Fetch emp_name, manager_id of all employees from employee_details table in descending order of manager_id. The query was as follows –

SELECT emp_name, manager_id FROM employee_details ORDER BY 
manager_id DESC; 

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

emp_name manager_id
Employee9 Employee4
Employee8 Employee4
Employee7 Employee3
Employee6 Employee3
Employee5 Employee3
Employee4 Employee2
Employee3 Employee1
Employee2
Employee1

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

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

SELECT * FROM employee_details 
WHERE dept_id = 2000 ORDER BY salary DESC, date_of_hire 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
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch rows in ascending order of a column and descending order of another column.

Requirement – Fetch emp_name, manager_id, designation of all employees from employee_details table in ascending order of manager_id and descending order of designation. The query was as follows –

SELECT emp_name, manager_id, designation FROM employee_details 
 ORDER BY manager_id, designation DESC; 

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

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