<

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

x

Order by Clause in Oracle Analytic Functions

Published on
3,128 Points
128 Views
Last Modified:
Editors:
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
Author:Swadhin Ray
0 Comments

Featured Post

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month