ORDER BY Clause in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

ORDER BY Clause in Oracle with Examples

In this article, I am going to discuss the ORDER BY Clause in Oracle with Examples. Please read our previous article where we discussed the WHERE Clause in Oracle with Examples. At the end of this article, you will understand the following pointers.

1. What is the ORDER BY Clause in Oracle?
2. Sorting without using ASC/DESC attribute in Oracle
3. Sorting in Descending Order using ORDER BY DESC 
4. ORDER BY Clause along with WHERE Clause
5. Order by Clause with Multiple Columns in Oracle
6. Sorting Data in Oracle by the Relative Position
7. What happens if the relative position does not exist?
8. Order By Clause on NULL columns

What is the ORDER BY Clause in Oracle?

The ORDER BY Clause in Oracle is used for sorting the data either in ascending or descending order based on a specified column or list of columns. That means if you want to sort the output or result of a query either in ascending or descending order then you need to use the ORDER BY Clause in oracle.

Syntax to use ORDER BY Clause in Oracle:

Following is the syntax to use ORDER BY Clause in Oracle.

Parameters or Arguments used with Order by Clause:

1. Expressions: The columns or calculations that you want to retrieve in the result set.
2. Tables: The tables from which you want to retrieve the records. There must be at least one table specified in the FROM clause.
3. WHERE Conditions: It is optional. The conditions must be met for the records to be selected by the query.
4. ASC: It is optional. If you want to sort the result set in ascending order of the expression then you need to use ASC. 
5. DESC: It is optional. If you want to sort the result set in descending order by expression then you need to use the DESC keyword.

Note: By default, Order by Clause in Oracle will sort the data in ascending order. If you want to arrange the data in descending order then you need to use the DESC keyword. The Order by Clause can be applied to any data type column in the table. This clause will arrange the data temporarily but not in the permanent store. The ORDER BY Clause in Oracle can only be used in SELECT Statements.

Example to understand ORDER BY Clause in Oracle:

We are going to use the following Employee table to understand the Order by Clause in Oracle with Examples.

Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data.

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Comm INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 3500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 4500, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 5000, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', NULL, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', NULL, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1008, 'Sambit', 'IT', 55000, 'Male', 5500, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1009, 'Pranaya', 'IT', 57000, 'Male', 5700, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Comm, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 7500, 'Mumbai');

Sorting without using ASC/DESC attribute in Oracle:

It is also possible in Oracle to use the ORDER BY clause without specifying the ASC or DESC keyword. When we have not specified the ASC or DESC attribute, in Oracle by default it will sort the data in ascending order. In the following example, we are using the ORDER BY clause on the Name column without specifying the ASC and DESC keywords. In this case, the records will be sorted in ascending order based on the Name column of the Employee table.

SELECT * FROM Employee ORDER BY Name;

Once you execute the above SELECT SQL query, you will get the following result set which is sorted based on the Name column in ascending order.

The below SQL Query will also give you the same result as the previous one. Here, in the following query, we explicitly specify the ASC keyword.

SELECT * FROM Employee ORDER BY Name ASC;

Sorting in Descending Order using ORDER BY DESC in Oracle:

If you want to sort the result set in descending order then you need to use the DESC attribute in your ORDER BY clause in Oracle. The following SQL Query will return all records from the Employee table sorted by the Name field in descending order.

SELECT * FROM Employee ORDER BY Name DESC;

When you execute the above SELECT Query, you will get the following result set which is sorted based on the Name column in Descending order.

ORDER BY Clause along with WHERE Clause in Oracle:

Let us see an example of how to use the ORDER BY Clause along with the WHERE clause in Oracle. In this case, first, the WHERE clause is going to be executed and the data get filtered. Then on the filtered data, the ORDER BY clause is applied. In the below query, first, it filtered the data based on the City column i.e. it fetches all the employees who belong to London and then applied for the ORDER BY clause on the Name column to sort the employees in ascending order.

SELECT * FROM Employee WHERE City = ‘London’ ORDER BY Name ASC;

Once you execute the above SELECT Query, you will get the following result set which includes the employees of London city and the employees are sorted in ascending order by name.

Order by Clause with Multiple Columns in Oracle:

It is also possible in Oracle to use the ORDER BY Clause with multiple columns. Let us understand this with an example. Our requirement is, first sort the employees by Department in Descending order and then sort the employees by name in Ascending order.

In this case, the order by clause going to contain two columns. Department column with DESC keyword and Name column with ASC keyword as shown in the below SQL Query. Here, first, the employees are sorted based on the first column i.e. Department, and then the employees are sorted based on the second column i.e. Name. But the point that you need to keep in mind is the second sorting is performed based on the first sorting i.e. for IT department employees it will sort the employees by name and again for HR department employees, it will sort by name and so on.

SELECT Id, Department, Name, Salary, City, Comm, Gender
FROM Employee
ORDER BY Department DESC, Name ASC;

Once you execute the above query, you will get the following output.

Sorting Data in Oracle by the Relative Position

We can also use the ORDER BY clause to sort the data by relative position in the result set, where the first field in the result is set to 1. The next field is 2, and so on. In the below example, the result set contains four columns. So, the relative position will be 1 for Name, 2 for Department, 3 for Gender, and 4 for City. Further, if you notice in the ORDER BY clause, we have specified 2 which means the following SQL Query will sort the data based on the Department column.

SELECT Name, Department, Gender, City FROM Employee ORDER BY 2 ASC;

Once you execute the above query, you will get the following output.

What happens if the relative position does not exist?

In that case, it will throw an error. If you specify 0, or any negative number, or a number that is more than the number of columns in the result set then you will get an error. In the below example, the select statement contains four columns and we are using 5 as the relative position to sort the data and hence when you try to execute the below query, you will get an error.

SELECT Name, Department, Gender, City FROM Employee ORDER BY 5 ASC;

When you try to execute the above query, it will give you the error like ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Order By Clause on NULL columns

When we use the ORDER BY clause on the “NULL” values column then oracle returns “NULL” values last in ascending order and “NULL” values are displayed first in descending by default. If we want to change this default order of “NULL” then we need to use null clauses i.e. NULLS FIRST and NULLS LAST.

SELECT * FROM Employee ORDER BY COMM NULLS FIRST;

Once you execute the above query, you will get the following output.

Example:
SELECT * FROM Employee ORDER BY COMM DESC NULLS LAST;
Output:

In the next article, I am going to discuss the GROUP BY Clause in Oracle with Examples. Here, in this article, I try to explain ORDER BY in Oracle with Examples and I hope you enjoy this ORDER BY Clause in Oracle with Examples article.