What is Order by Clause?
Order by clause is used to filter or sort the data in order or used to imposing the ordering of the data that is used or required as per our need. It is also used in analytical functions in order to work more effectively.
Syntax:
ANALYTIC FUNCTION () OVER (ORDER BY <<COLUMN NAME >>)
The above syntax diagram is taken from Oracle SQL Reference 18c document.
Let us take the following as an example.
If I wanted to find employee's salaries from a particular department in descending order, then I would need to show the highest salary first, followed by the lower salary in order.
To achieve this we can use an analytical function i.e. DENSE_RANK function which will be used to give a rank to my rows.
SELECT FIRST_NAME
,LAST_NAME , SALARY, DEPARTMENT_ID, DENSE_RANK() OVER
(ORDER BY salary ) RANK
from EMPLOYEES WHERE DEPARTMENT_ID=60;
Output:
FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID RANK
------------------------------ ------------------------- ---------- --------------- ----------
Diana Lorentz 4200 60 1
David Austin 4800 60 2
Valli Pataballa 4800 60 2
Bruce Ernst 6000 60 3
Alexander Hunold 9000 60 4
We can see the output, but it's in reverse order because if we didn't provide the condition for descending order. By default, Oracle will treat the column "Salary" in ascending order.
Let's modify the code and execute it again.
SELECT FIRST_NAME
,LAST_NAME , SALARY, DEPARTMENT_ID, DENSE_RANK() OVER
(ORDER BY salary DESC) RANK
from EMPLOYEES WHERE DEPARTMENT_ID=60;
Expected output:
FIRST_NAME LAST_NAME SALARY DEPARTMENT_ID RANK
------------------------------ ------------------------- ---------- --------------- ----------
Alexander Hunold 9000 60 1
Bruce Ernst 6000 60 2
David Austin 4800 60 3
Valli Pataballa 4800 60 3
Diana Lorentz 4200 60 4
Now we can see the Ranks of the employees under department ID 60 with highest to lowest salaries. The above code (Order by Clause) is used to order the salary in descending order and the DENSE_RANK function is used to determine the Rank to each row.
We also see that the Rank "3" is assigned to two rows because they have the same salary, and the analytic function assigns a joint rank to the same rows. We cannot execute the Rank and DENSE_RANK functions without order by clause.
Here are some analytical functions which required "ORDER BY CLAUSE" to work properly:
So now that we understand how the input data is being sorted using order by a clause in Oracle, I would suggest having a look at my other articles that were published.
Thank you for reading my article, please feel free to leave me some feedback or to suggest any future topics.
I'll be looking forward to hearing from you – Swadhin Ray (Sloba)
For more information about me, please check out my Experts Exchange Profile page.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)