ORDERPLSQL

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’.