<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Order by Clause in Oracle Analytic Functions

Published on
3,051 Points
51 Views
Last Modified:
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.


Edited by: Andrew Leniart

0
Comment
Author:Swadhin Ray
0 Comments

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month