SQL ORDER BY DESC
SQL stands for Structured Query Language, and it is used to handle data in RDBMS (relational databases). Several relational database management systems use it as a common language to communicate with the data contained in the database.
The capacity to sort data stored in a database based on numerous criteria is a core feature of SQL. In SQL, the "ORDER BY" phrase serves to sort data in ascending or descending order. In this post, we'll look at the "ORDER BY DESC" clause and see how it operates through examples.
SQL ORDER BY Clause
The SQL ORDER BY phrase can be used to arrange the SELECT query results. The SELECT statement is used to extract information from one or more database tables. The ORDER BY phrase arranges the data depending on one or more table columns.
The data is sorted in ascending order by default, but it may be sorted in descending order by using the "DESC" keyword.
The following is the syntax for the ORDER BY clause:
SELECT column1, column2, column3 FROM table_name ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], column3 [ASC | DESC];
The "SELECT" expression in the preceding syntax pulls data from the table "table name," and the "ORDER BY" clause arranges the data depending on the columns supplied in the SELECT statement. The "ASC" keyword can be used to sort data ascendingly, whereas the "DESC" keyword can be used to sort data descendingly.
SQL ORDER BY DESC
In the SQL ORDER BY clause, the "DESC" keyword can be used to organise the data in descending order. In other words, the data is arranged in reverse order, with the greatest values displayed initially and the lowest values displayed last.
The SQL ORDER BY DESC clause is written as follows:
SELECT column1, column2, column3 FROM table_name ORDER BY column1 DESC, column2 DESC, column3 DESC;
In the above syntax, the data is ordered in decreasing order depending on the columns supplied in the SELECT query.
SQL ORDER BY DESC Examples
Consider the following table, "Employees" to demonstrate how the SQL ORDER BY DESC clause operates using examples.
Employee_Id | Employee_Name | Employee_Salary | Employee_Age |
1 | Rahul Sharma | 50000 | 28 |
2 | Priya Patel | 75000 | 35 |
3 | Rajesh Singh | 65000 | 42 |
4 | Anjali Gupta | 80000 | 29 |
5 | Sanjay Mehta | 60000 | 31 |
6 | Nisha Shah | 55000 | 26 |
7 | Aakash Verma | 70000 | 39 |
8 | Kavita Reddy | 85000 | 33 |
9 | Rohit Kumar | 45000 | 24 |
10 | Sunita Rao | 90000 | 37 |
Case 1: Sorting the data based on the "Employee_Salary" column in order of decreasing
The SQL query sorts the data in decreasing order using the "Employee_Salary" column.
Query:
SELECT Employee_Id, Employee_Name, Employee_Age, Employee_Salary
FROM Employees
ORDER BY Employee_Salary DESC;
Output:
The following is the result of the mentioned SQL statement:
![SQL ORDER BY DESC](https://static.tutorialandexample.com/sql/sql-order-by-desc1.png)
Case 2: Sorting data in decreasing order based on numerous columns
The SQL query below arranges the data in decreasing order using the "Employee_Salary" and "Employee_Age" columns.
Query:
SELECT Employee_ID, Employee¬_Name, Employee_Age, Employee_Salary
FROM Employees
ORDER BY Employee_Salary DESC, Employee_Age DESC
Output:
The following is the result of the above SQL statement:
![SQL ORDER BY DESC](https://static.tutorialandexample.com/sql/sql-order-by-desc2.png)
In the above case, the data is first sorted in decreasing order by the "Employee_Salary" column. If there are numerous employees who have the same income, the data is sorted in decreasing order based on the "Employee_Age" column.
Case 3: Sorting data in descending order based on different data types in columns
In some cases, the data in a database table may include columns of varying data types. Take the "Student_data" table, which includes student data with columns "Student_id" (integer data type), "Student_name" (string data type), "Student_age" (integer data type), "Student_grade" (integer data type), and "Student_city" (string data type).
Student_id | Student_name | Student_age | Student_grade | Student_city |
1 | Emma Johnson | 17 | A | New York |
2 | Oliver Williams | 16 | B | Los Angeles |
3 | Ava Davis | 16 | B | Chicago |
4 | William Wilson | 15 | C | Houston |
5 | Sophia Jackson | 18 | A | Phoenix |
6 | James Thompson | 17 | B | Philadelphia |
7 | Isabella Brown | 15 | C | San Antonio |
8 | Michael Martinez | 16 | B | San Diego |
9 | Charlotte Garcia | 15 | C | Dallas |
10 | Ethan Rodriguez | 17 | A | San Francisco |
The SQL query sorts the data in decreasing order using the "Student_grade" column:
Query:
SELECT student_id, student_name, student_age, student_grade, student_city
FROM student_data
ORDER BY student_grade DESC;
Output:
The following is the result of the mentioned SQL statement:
![SQL ORDER BY DESC](https://static.tutorialandexample.com/sql/sql-order-by-desc3.png)
In this case, the data is ordered descendingly by the "Student_grades" column. The student with the highest grade point average (GPA) appears first, while the student with the lowest grade point average (GPA) appears last.
Case 4: Sorting data in decreasing order based on NULL values in columns
The data in a database table may contain NULL values in various instances. In such circumstances, we may sort the data in descending order by using the SQL IS NULL and IS NOT NULL operators.
Consider the table "Employees" which contains employee data with columns "Employee_id" (integer data type),
"Employee_name" (string data type),
"Employee_age" (integer data type),
"Employee_salary" (integer data type),
and "Employee_position" (string data type).
Employee_Id | Employee_Name | Employee_Salary | Employee_Age | Employee_Position |
1 | Rahul Sharma | 50000 | 28 | Manager |
2 | Priya Patel | 75000 | 35 | Senior Engineer |
3 | Rajesh Singh | 65000 | 42 | Marketing Executive |
4 | Anjali Gupta | 80000 | 29 | Software Developer |
5 | Sanjay Mehta | 60000 | 31 | Product Manager |
6 | Nisha Shah | 55000 | 26 | Sales Executive |
7 | Aakash Verma | 70000 | 39 | Account Manager |
8 | Kavita Reddy | 85000 | 33 | Lead Engineer |
9 | Rohit Kumar | 45000 | 24 | Operations Manager |
10 | Sunita Rao | 90000 | 37 | Human Resource Manager |
The below SQL query arranges the data in decreasing order using the "employee salary" column:
SELECT employee_id, employee_name, employee_age, employee_salary, employee_position
FROM employees
WHERE employee_salary IS NOT NULL
ORDER BY employee_salary DESC;
Output:
The following is the result of the mentioned SQL statement:
![SQL ORDER BY DESC](https://static.tutorialandexample.com/sql/sql-order-by-desc4.png)
The data is sorted in decreasing order in this case based on the "Employee_salary" column. The employee with the greatest income is displayed first, followed by the employee with the lowest salary. To exclude NULL data from the sorting process, the SQL IS NOT NULL operator is utilised.
Case 5: Sorting data in decreasing order based on date data type columns
Date columns may exist in a database table in some instances. In such circumstances, we can sort the data in decreasing order using the SQL DATE data type.
Consider the "Order_data" table, which includes order data with the columns "Order_id" (integer data type), "Order_date" (date data type), "Order_amount" (integer data type), and "Customer_Id" (string data type).
Order_Id | Order_Date | Order_Amount | Customer_Id |
1 | 2022-02-15 | 1350.75 | 102 |
2 | 2022-01-05 | 542.90 | 125 |
3 | 2022-03-02 | 998.50 | 114 |
4 | 2022-02-18 | 720.00 | 108 |
5 | 2022-03-15 | 285.30 | 132 |
6 | 2022-01-25 | 1675.25 | 107 |
7 | 2022-02-10 | 890.40 | 119 |
8 | 2022-03-09 | 421.60 | 129 |
9 | 2022-01-20 | 599.99 | 101 |
10 | 2022-02-28 | 1750.00 | 112 |
The SQL query below sorts the data in decreasing order using the "Order_date" column:
Query:
SELECT order_id, order_date, order_amount, customer_id
FROM order_data
ORDER BY order_date DESC;
Output:
The following is the result of the mentioned SQL statement:
![SQL ORDER BY DESC](https://static.tutorialandexample.com/sql/sql-order-by-desc5.png)
In this case, the data is ordered descendingly by the "Order_date" column. The most current order shows first, followed by the oldest order.
Conclusion
In SQL, the ORDER BY DESC clause is used to sort data in descending order. The data is sorted in reverse order, with the highest values first and the lowest values last.
To obtain and sort data from a database, use the SQL ORDER BY DESC clause in conjunction with the SELECT command.
The data can be sorted by one or even more columns, and it can be sorted in decreasing order.
Understanding the SQL ORDER BY DESC clause and how it works is critical for properly managing data in a relational database.