ORDER BY Clause in Oracle SQL – PLSQL

The ORDER BY clause in Oracle SQL / PLSQL is used in SELECT statement and allows us to sort the records fetched by SELECT statement.

Syntax for the ORDER BY clause in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE conditions
ORDER BY column(s) ASC/DESC;

The ORDER BY clause will sort the result in ascending order (if ASC is mentioned) or in descending order (if DESC is mentioned) after the column_name in ORDER BY clause.
If nothing is mentioned after the column_name in ORDER BY clause, by default ascending order (ASC) is taken into consideration.

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Suppose we want to see records from ‘employee’ table sorted by ‘department’

We can achieve the same as:

SELECT *
FROM employee
ORDER BY department;

The result of the above query will be:

Employee_ID Employee_Name Salary Department Commission
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
105 Emp E 32000 Sales 10
101 Emp A 10000 Sales 10
104 Emp D 30000 Support

Here we can see that the records have been fetched as per ascending order of the ‘department’.


Scenario 2:

Suppose we want to see the list of employees in reverse order of ‘employee_id’
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

We can achieve the same as:

SELECT *
FROM employee
ORDER BY employee_id  DESC;

Once we have run the above code following will be the result:

Employee_ID Employee_Name Salary Department Commission
105 Emp E 32000 Sales 10
104 Emp D 30000 Support
103 Emp C 28000 IT 20
102 Emp B 20000 IT 20
101 Emp A 10000 Sales 10

Here we can see that the records have been fetched as per descending order of ‘employee_id’.


Tagged , , , , , , , , , , , , . Bookmark the permalink.