Order by Clause in Oracle Analytic Functions

Swadhin Ray
CERTIFIED EXPERT
Published:
Updated:
Edited by: Andrew Leniart
In this article, we will see how to use Order by Clause in Oracle with simple examples.

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:


  • CUME_DIST
    • CUME_DIST() OVER (PARTITION BY <<column name>> ORDER BY <<column name>>)
  • DENSE_RANK
    • DENSE_RANK() OVER (PARTITION BY <<column name>> ORDER BY <<column name>>)
  • NTILE
    • NTILE(4) OVER (ORDER BY <<column name>> DESC)
  • PERCENT_RANK
    • PERCENT_RANK() WITHIN GROUP (ORDER BY <<column name>>)
  • RANK
    • RANK() WITHIN GROUP (ORDER BY  <<column name>> DESC/ASC)


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.



0
2,332 Views
Swadhin Ray
CERTIFIED EXPERT

Comments (0)

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.