Oracle ORDER BY DESC

Introduction to Oracle ORDER BY DESC

ORDER BY clause can be defined as a clause which is used to query the result set or rows in either ascending order or descending order with respect to a column because the rows or records stored in oracle database are stored in an unspecified order and when the word DESC is added to the ORDER BY clause the definition slightly changes to query the rows in descending order otherwise the default with ORDER BY is ascending order.

Syntax:

In the previous section, we discussed about the definition of the ORDER BY DESC clause. Let us now look at the syntax for the same.

SELECT
column1,
column2
column_n
FROM
table_name
ORDER BY column_name DESC;

Parameters:

• column1, column2, column_n: This refers to the columns whose data we want.
• table_name: It refers to the name of the table which we want to query
• column_name: It refers to the column based on which we want to sort the values in descending order.

How Does ORDER BY DESC Work in Oracle?

In general, the data in the Oracle database is not stored in a specified order (ascending or descending). Let us now discuss how the ORDER BY clause works in the oracle database. On executing a select query which has an order by clause, the database first retrieves the rows of the columns based from the table in case there is no WHERE condition present but if there is a WHERE condition present in the query, the WHERE condition is first executed and based on that the rows of the columns which satisfies the where the condition is retrieved. This forms the result set of the query. The ORDER BY DESC clause is now applied on this result set and then oracle sorts the result set in descending order based on the column named in the ORDER BY clause. In case the DESC keyword is not there, by default database would have done the sorting in ascending order.

Examples to Implement ORDER BY DESC in Oracle

Let us now look at a few examples so that we can have a better understanding:

Example #1 – Sorting Rows BY Descending ORDER without Condition

In this example, we are going to sort the rows in the descending using ORDER BY DESC clause. For this example, we are going to sort the content of the employee table by city column in descending order. Since there is no WHERE condition the result set will consist of all the values present in the table. Let us look at the query for the same.

Query:

SELECT employee_id, employee_name,
city, salary FROM employee
ORDER BY city DESC;

Output:

As we can see in the screenshot above the output displays the values by the city column in descending order.

Example #2 – Sorting Rows BY Descending ORDER with WHERE Condition

In this example we are going to sort the rows in the descending using ORDER BY DESC clause with WHERE condition. For this example, we are going to sort the content of the employee table by employee name in descending order for those employees whose city is Lucknow. Since there is WHERE condition the result set will consist of the rows whose value in the city column is Lucknow. Let us prepare the SELECT query for the same.

Query:

SELECT employee_id, employee_name,
city, salary FROM employee
WHERE city ='Lucknow'
ORDER BY employee_name DESC;

Output:

As we can see in the screenshot the above output displays the values by the employee_name column in descending order where the city is Lucknow.

Example #3 – Sorting Rows ORDER BY Descending on Multiple Columns

In the earlier examples, we were using the order by clause on a single column but in this example, we are going to apply it on multiple columns. In this example, we are going to sort the values from the employee table by the employee name in ascending order and salary in descending order. We will not use anywhere condition in this case so the result set will consist of all the values in the table employee. Let us prepare a SELECT query for the same.

Query:

SELECT employee_id, employee_name,
city, salary FROM employee
ORDER BY employee_name,
SALARY DESC;

Output:

In the query above the Oracle, firsts sort the rows by employee name to prepare an initial list, and then the initial list is again sorted by salary in descending order for the final list.

As we can see in the screenshot above the output displays the values by the employee_name column in ascending order and then by salary column in descending order.

Example #4 – Sorting Rows ORDER BY Descending on Column’s Position

In the earlier examples, we were using the order by clause on a single column but in this example, we are going to apply it on multiple columns by using the column’s position. In this example, we are going to sort the values from the employee table by the employee name in ascending order and salary in descending order but by using the respective position of the columns. Let us prepare a SELECT query for the same.

Query:

SELECT employee_id, employee_name,
city, salary FROM employee
ORDER BY 4 DESC, 2;

Output:

In the query above we have replaced the column names with column position. The Oracle firsts sorts the rows by column in position four in descending order to prepare an initial list and then the initial list is again sorted by column in position two in ascending order.

As we can see the screenshot above the output displays the sorted result.

Conclusion

In this article, we discussed about the ORDER BY clause DESC in detail. At first, we discussed the definition of the ORDER BY clause and then the syntax along with working. Later we went through examples across all scenarios to understand better.

Recommended Articles

This is a guide to Oracle ORDER BY DESC. Here we discuss the Introduction of Oracle ORDER BY DESC and its syntax along with the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. Introduction to Oracle Aliases 
2. How Does Left Join Works in Oracle?
3. What Are Oracle Database | Applications
4. To 10 Oracle Database Interview Questions