- Whenever we want to sort the records based on the columns stored in the tables of the SQL database, then we consider using the ORDER BY clause in SQL.
- The ORDER BY clause in SQL helps us to sort the records based on the specific column of a table. This means that initially, all the values stored in the column on which we are applying the ORDER BY clause will be sorted. Then the corresponding column values will be displayed in the same sequence in which the values we have obtained in the earlier step.
- Using the ORDER BY clause, we can sort the records in ascending or descending order as per our requirement. The records will be sorted in ascending order whenever the ASC keyword is used with the ORDER by clause. DESC keyword will sort the records in descending order. If no keyword is specified after the column based on which we have to sort the records, then, in that case, the sorting will be done by default in the ascending order.
Before writing the queries for sorting the records, let us understand the syntax.
Syntax to sort the records in descending order:
SELECT ColumnName1,…,ColumnNameN FROM TableName ORDER BY ColumnNameDESC; Let us explore more on this topic with the help of examples. We will use the MySQL database for writing the queries in examples.
Consider we have customers table with the following records:
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Himani Gupta | 21 | Modinagar | 22000 |
| 2 | Shiva Tiwari | 22 | Bhopal | 21000 |
| 3 | Ajeet Bhargav | 45 | Meerut | 65000 |
| 4 | Ritesh Yadav | 36 | Azamgarh | 26000 |
| 5 | Balwant Singh | 45 | Varanasi | 36000 |
| 6 | Mahesh Sharma | 26 | Mathura | 22000 |
| 7 | Rohit Shrivastav | 19 | Ahemdabad | 38000 |
| 8 | Neeru Sharma | 29 | Pune | 40000 |
| 9 | Aakash Yadav | 32 | Mumbai | 43500 |
| 10 | Sahil Sheikh | 35 | Aurangabad | 68800 |
Example 1:
Write a query to sort the records in the descending order of the customer names stored in the customers table.
Query:
mysql> SELECT *FROM customers ORDER BY Name DESC; Here in a SELECT query, an ORDER BY clause is applied on the column ‘Name’ to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 2 | Shiva Tiwari | 22 | Bhopal | 21000 |
| 10 | Sahil Sheikh | 35 | Aurangabad | 68800 |
| 7 | Rohit Shrivastav | 19 | Ahemdabad | 38000 |
| 4 | Ritesh Yadav | 36 | Azamgarh | 26000 |
| 8 | Neeru Sharma | 29 | Pune | 40000 |
| 6 | Mahesh Sharma | 26 | Mathura | 22000 |
| 1 | Himani Gupta | 21 | Modinagar | 22000 |
| 5 | Balwant Singh | 45 | Varanasi | 36000 |
| 3 | Ajeet Bhargav | 45 | Meerut | 65000 |
| 9 | Aakash Yadav | 32 | Mumbai | 43500 |
All the records present in the customers table are displayed in the descending order of the customer’s name.
Example 2:
Write a query to sort the records in the descending order of the addresses stored in the customers table.
Query:
mysql> SELECT *FROM customers ORDER BY Address DESC; Here in a SELECT query, an ORDER BY clause is applied to the ‘Address’ column to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 5 | Balwant Singh | 45 | Varanasi | 36000 |
| 8 | Neeru Sharma | 29 | Pune | 40000 |
| 9 | Aakash Yadav | 32 | Mumbai | 43500 |
| 1 | Himani Gupta | 21 | Modinagar | 22000 |
| 3 | Ajeet Bhargav | 45 | Meerut | 65000 |
| 6 | Mahesh Sharma | 26 | Mathura | 22000 |
| 2 | Shiva Tiwari | 22 | Bhopal | 21000 |
| 4 | Ritesh Yadav | 36 | Azamgarh | 26000 |
| 10 | Sahil Sheikh | 35 | Aurangabad | 68800 |
| 7 | Rohit Shrivastav | 19 | Ahemdabad | 38000 |
All the records present in the customers table are displayed in the descending order of the customer’s address.
Example 3:
Write a query to sort the records in the descending order of the customer salary stored in the customers table.
Query:
mysql> SELECT *FROM customers ORDER BY Salary DESC; Here in a SELECT query, an ORDER BY clause is applied on the column ‘Salary’ to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| ID | Name | Age | Address | Salary |
|---|---|---|---|---|
| 10 | Sahil Sheikh | 35 | Aurangabad | 68800 |
| 3 | Ajeet Bhargav | 45 | Meerut | 65000 |
| 9 | Aakash Yadav | 32 | Mumbai | 43500 |
| 8 | Neeru Sharma | 29 | Pune | 40000 |
| 7 | Rohit Shrivastav | 19 | Ahemdabad | 38000 |
| 5 | Balwant Singh | 45 | Varanasi | 36000 |
| 4 | Ritesh Yadav | 36 | Azamgarh | 26000 |
| 6 | Mahesh Sharma | 26 | Mathura | 22000 |
| 1 | Himani Gupta | 21 | Modinagar | 22000 |
| 2 | Shiva Tiwari | 22 | Bhopal | 21000 |
All the records present in the customers table are displayed in the descending order of the customer’s salary.
Example 4:
Write a query to sort the records in the descending order of the customer age stored in the customers table.
Query:
mysql> SELECT *FROM customers ORDER BY Age DESC; Here in a SELECT query, an ORDER BY clause is applied on the column ‘Age’ to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| ID | Name | Age | Address | Salary |
|---|---|---|---|---|
| 3 | Ajeet Bhargav | 45 | Meerut | 65000 |
| 5 | Balwant Singh | 45 | Varanasi | 36000 |
| 4 | Ritesh Yadav | 36 | Azamgarh | 26000 |
| 10 | Sahil Sheikh | 35 | Aurangabad | 68800 |
| 9 | Aakash Yadav | 32 | Mumbai | 43500 |
| 8 | Neeru Sharma | 29 | Pune | 40000 |
| 6 | Mahesh Sharma | 26 | Mathura | 22000 |
| 2 | Shiva Tiwari | 22 | Bhopal | 21000 |
| 1 | Himani Gupta | 21 | Modinagar | 22000 |
| 7 | Rohit Shrivastav | 19 | Ahemdabad | 38000 |
All the records present in the customers table are displayed in the descending order of the customer’s age.
Consider we have another table named agents with the following records:
| AID | Name | WorkArea | Profit_Percent | ContactNumber | Salary |
|---|---|---|---|---|---|
| 1 | Gurpreet Singh | Bangalore | 1 | 9989675432 | 43000 |
| 2 | Sakshi Kumari | Chennai | 5 | 8190567342 | 25000 |
| 3 | Prachi Desai | Mumbai | 2 | 9056123432 | 60000 |
| 4 | Shivani More | Pune | 3 | 8894236789 | 35500 |
| 5 | Pallavi Singh | Delhi | 4 | 7798092341 | 38700 |
| 6 | Rohini Kulkarni | Ambala | 8 | 7890945612 | 25670 |
| 7 | Shweta Dixit | Chandigarh | 6 | 8898786453 | 31670 |
| 8 | Sonakshi Tiwari | Udaipur | 2 | 9809453421 | 25050 |
| 9 | Anushka Tripathi | Ujjain | 9 | 8909124326 | 38000 |
| 10 | Devika Sharma | Goa | 7 | 7864523145 | 44050 |
Example 1:
Write a query to sort the records in the descending order of the agent names stored in the agents table.
Query:
mysql> SELECT *FROM agents ORDER BY Name DESC; Here in a SELECT query, an ORDER BY clause is applied on the column ‘Name’ to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| AID | Name | WorkArea | Profit_Percent | ContactNumber | Salary |
|---|---|---|---|---|---|
| 8 | Sonakshi Tiwari | Udaipur | 2 | 9809453421 | 25050 |
| 7 | Shweta Dixit | Chandigarh | 6 | 8898786453 | 31670 |
| 4 | Shivani More | Pune | 3 | 8894236789 | 35500 |
| 2 | Sakshi Kumari | Chennai | 5 | 8190567342 | 25000 |
| 6 | Rohini Kulkarni | Ambala | 8 | 7890945612 | 25670 |
| 3 | Prachi Desai | Mumbai | 2 | 9056123432 | 60000 |
| 5 | Pallavi Singh | Delhi | 4 | 7798092341 | 38700 |
| 1 | Gurpreet Singh | Bangalore | 1 | 9989675432 | 43000 |
| 10 | Devika Sharma | Goa | 7 | 7864523145 | 44050 |
| 9 | Anushka Tripathi | Ujjain | 9 | 8909124326 | 38000 |
All the records present in the agents table are displayed in the descending order of the agent’s name.
Example 2:
Write a query to sort the records in the descending order of the agent salary stored in the agents table.
Query:
mysql> SELECT *FROM agents ORDER BY Salary DESC; Here in a SELECT query, an ORDER BY clause is applied on the column ‘Salary’ to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| AID | Name | WorkArea | Profit_Percent | ContactNumber | Salary |
|---|---|---|---|---|---|
| 3 | Prachi Desai | Mumbai | 2 | 9056123432 | 60000 |
| 10 | Devika Sharma | Goa | 7 | 7864523145 | 44050 |
| 1 | Gurpreet Singh | Bangalore | 1 | 9989675432 | 43000 |
| 5 | Pallavi Singh | Delhi | 4 | 7798092341 | 38700 |
| 9 | Anushka Tripathi | Ujjain | 9 | 8909124326 | 38000 |
| 4 | Shivani More | Pune | 3 | 8894236789 | 35500 |
| 7 | Shweta Dixit | Chandigarh | 6 | 8898786453 | 31670 |
| 6 | Rohini Kulkarni | Ambala | 8 | 7890945612 | 25670 |
| 8 | Sonakshi Tiwari | Udaipur | 2 | 9809453421 | 25050 |
| 2 | Sakshi Kumari | Chennai | 5 | 8190567342 | 25000 |
All the records present in the agents table are displayed in the descending order of the agent’s salary.
Example 3:
Write a query to sort the records in the descending order of the agent’s work area stored in the agents table.
Query:
mysql> SELECT *FROM agents ORDER BY WorkArea DESC; Here in a SELECT query, an ORDER BY clause is applied on the column ‘WorkArea’ to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| AID | Name | WorkArea | Profit_Percent | ContactNumber | Salary |
|---|---|---|---|---|---|
| 9 | Anushka Tripathi | Ujjain | 9 | 8909124326 | 38000 |
| 8 | Sonakshi Tiwari | Udaipur | 2 | 9809453421 | 25050 |
| 4 | Shivani More | Pune | 3 | 8894236789 | 35500 |
| 3 | Prachi Desai | Mumbai | 2 | 9056123432 | 60000 |
| 10 | Devika Sharma | Goa | 7 | 7864523145 | 44050 |
| 5 | Pallavi Singh | Delhi | 4 | 7798092341 | 38700 |
| 2 | Sakshi Kumari | Chennai | 5 | 8190567342 | 25000 |
| 7 | Shweta Dixit | Chandigarh | 6 | 8898786453 | 31670 |
| 1 | Gurpreet Singh | Bangalore | 1 | 9989675432 | 43000 |
| 6 | Rohini Kulkarni | Ambala | 8 | 7890945612 | 25670 |
All the records present in the agents table are displayed in the descending order of the agent’s workarea.
Example 4:
Write a query to sort the records in the descending order of the agent’s profit percentage stored in the agents table.
Query:
mysql> SELECT *FROM agents ORDER BY Profit_Percent DESC; Here in a SELECT query, an ORDER BY clause is applied on the column ‘Profit_Percent’ to sort the records. DESC keyword will sort the records in descending order.
You will get the following output:
| AID | Name | WorkArea | Profit_Percent | ContactNumber | Salary |
|---|---|---|---|---|---|
| 9 | Anushka Tripathi | Ujjain | 9 | 8909124326 | 38000 |
| 6 | Rohini Kulkarni | Ambala | 8 | 7890945612 | 25670 |
| 10 | Devika Sharma | Goa | 7 | 7864523145 | 44050 |
| 7 | Shweta Dixit | Chandigarh | 6 | 8898786453 | 31670 |
| 2 | Sakshi Kumari | Chennai | 5 | 8190567342 | 25000 |
| 5 | Pallavi Singh | Delhi | 4 | 7798092341 | 38700 |
| 4 | Shivani More | Pune | 3 | 8894236789 | 35500 |
| 3 | Prachi Desai | Mumbai | 2 | 9056123432 | 60000 |
| 8 | Sonakshi Tiwari | Udaipur | 2 | 9809453421 | 25050 |
| 1 | Gurpreet Singh | Bangalore | 1 | 9989675432 | 43000 |
All the records present in the agents table are displayed in the descending order of the agent’s profit percent.
Leave a Reply