Introduction to Rank Function in Oracle

In this blog, we will see how to use the RANK analytical function in an Oracle database and how it helps us in an analysis.

What is the RANK function?

The RANK function is an Oracle analytical function which calculates the rank of values which are retrieved, or against a set of rows. The same values will share the same rank for a group of rows or the subsequent ranks as they are tied to the values so it might not be consecutive in sequence or number, as they may vary in each case.

Analytical functions are really helpful when we try to use them for reporting purpose and helps in taking business decisions.  To use the RANK function we need to use the “ORDER BY CLAUSE“.

You can learn more about the “Order by Clause” by clicking this link to another one of my articles.

The RANK function will be used after the “ORDER BY” clause is been applied to the rows, as shown in below syntax.

The syntax for RANK Function:

 RANK() WITHIN GROUP (ORDER BY  <<column name>> DESC/ASC) 

In RANK analytical functions we don’t need PARTITIONED BY  conditions to execute successfully.

Let us take an example on getting the ranks of the employees based on the departments.

SELECT FIRST_NAME ,LAST_NAME , SALARY, DEPARTMENT_ID, RANK() OVER ( ORDER BY salary DESC) "Rank" from EMPLOYEES  WHERE DEPARTMENT_ID =50 ;

Output:

FIRST_NAME                     LAST_NAME                     SALARY   DEPARTMENT_ID       Rank
------------------------------ ------------------------- ---------- --------------- ----------
Adam                           Fripp                           8200              50          1
Matthew                        Weiss                           8000              50          2
Payam                          Kaufling                        7900              50          3
Shanta                         Vollman                         6500              50          4
Kevin                          Mourgos                         5800              50          5
Nandita                        Sarchand                        4200              50          6
Alexis                         Bull                            4100              50          7
Sarah                          Bell                            4000              50          8
Britney                        Everett                         3900              50          9
Kelly                          Chung                           3800              50         10
Renske                         Ladwig                          3600              50         11 Jennifer                       Dilly                           3600              50         11 Trenna                         Rajs                            3500              50         13 Julia                          Dellinger                       3400              50         14
Laura                          Bissot                          3300              50         15
Jason                          Mallin                          3300              50         15
Winston                        Taylor                          3200              50         17
Samuel                         McCain                          3200              50         17
Stephen                        Stiles                          3200              50         17
Julia                          Nayer                           3200              50         17
Curtis                         Davies                          3100              50         21
Jean                           Fleaur                          3100              50         21

From the above output, we can see the that we don’t see the RANKS assigned is consecutive in sequence. We can see from the highlighted rows. In this case we see that the RANK 11 is assigned to two employees based on the salary and as they are getting the same salary, it has assigned one rank but the sequence is broken here having 11 in place of 12, which is missing from the assigned ranks and the next number i.e. 13 is assigned to the next row.

Similarly, we see the same scenario for the other rows too.  Here we are only checking for a specific department number. In a case where we need to show the similar results for all the departments, then we can use a partition by clause to the RANK function and the SQL query will be as below:

SELECT FIRST_NAME 
,LAST_NAME , SALARY, DEPARTMENT_ID, RANK() OVER ( PARTITION by department_id ORDER BY salary DESC) "Rank"
from EMPLOYEES ;

Expected result:

FIRST_NAME                     LAST_NAME                     SALARY   DEPARTMENT_ID       Rank
------------------------------ ------------------------- ---------- --------------- ----------
Jennifer                       Whalen                          4400              10          1
Michael                        Hartstein                      13000              20          1
Pat                            Fay                             6000              20          2
Den                            Raphaely                       11000              30          1
Alexander                      Khoo                            3100              30          2
Shelli                         Baida                           2900              30          3
Sigal                          Tobias                          2800              30          4
Guy                            Himuro                          2600              30          5
Karen                          Colmenares                      2500              30          6
Susan                          Mavris                          6500              40          1
Adam                           Fripp                           8200              50          1
Matthew                        Weiss                           8000              50          2
Payam                          Kaufling                        7900              50          3
Shanta                         Vollman                         6500              50          4
Kevin                          Mourgos                         5800              50          5
Nandita                        Sarchand                        4200              50          6
Alexis                         Bull                            4100              50          7
Sarah                          Bell                            4000              50          8
Britney                        Everett                         3900              50          9
Kelly                          Chung                           3800              50         10
Jennifer                       Dilly                           3600              50         11
Renske                         Ladwig                          3600              50         11
Trenna                         Rajs                            3500              50         13
Julia                          Dellinger                       3400              50         14
Laura                          Bissot                          3300              50         15
Jason                          Mallin                          3300              50         15
Stephen                        Stiles                          3200              50         17
Samuel                         McCain                          3200              50         17
Julia                          Nayer                           3200              50         17
Winston                        Taylor                          3200              50         17
Jean                           Fleaur                          3100              50         21
Curtis                         Davies                          3100              50         21
Alana                          Walsh                           3100              50         21

So now we understand how to use the RANK function in Oracle.

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

 

Advertisements

Partition By Clause in Oracle

An introductory discussion about how to use Partition by Clause in Oracle.

What is a “partition by” clause in Oracle?

It is used to break the data into small partitions and is been separated by a boundary or in simple dividing the input into logical groups. The analytical functions are performed within this partitions.  So when the boundaries are crossed then the function get restarted to segregate the data. The “partition by” clause is similar to the “GROUP BY” clause that is used in aggregate functions. They are also known as query partition clause in Oracle.

The syntax of PARTITION BY CLAUSE:

# ANALYTIC FUNCTION or FUNCTION NAME () OVER ( PARTITION BY COLUMN NAME )

Any arguments that need to be passed to the functions then it has to be passed within the parenthesis and after which we have to write the keyword OVER where the partition by will be used.   The column name is used where we want to use clause.

Example of PARTITION BY CLAUSE:

Let us try to identify the total or cumulative cost of each department when it comes to expenditure on salary.  To show this let us take the example from the Employee table which stores the data of the employee, department ID and salary etc..………continue reading from my original article that was published on Experts-Exchange.com and provides about how to use Partition by Clause in Oracle.

 

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Installing Oracle 18c on Windows Operating system

In this article, we will see how to install Oracle 18c on the Windows operating system.  To install 18c first we need to download the setup from Oracle.com. In my case, I have already downloaded and extracted locally on my windows machine.

The below file will show the step by step guide on how to install Oracle 18c enterprise edition on Windows environment.

Install 18c on Windows

 

Thank you for reading my blog, please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

 

Tuples in Python

In this article, we will see what are Tuple and how we use them in Python programming.

Tuples:

 

Tuples are one of the built-in types of sequences out of six and just like lists. There is another article published on how to use Lists in Python. We want to store the values for the rest of our program then we can use Tuples.  Now for an example, if we want to store the name of some category of any Test plan when comes to compliance like “Audit, SOX, Risk Management or ORM” for continuing some program.

First, we need to declare a tuple and give it a name, the initial values will be used within parentheses “()”. All the values will be separated by a comma………continue reading from my original article that was published on Experts-Exchange.com and provides a basic explanation on how to use Tuples in Python programming language.

 

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Logical Operators in Python

In this article, we will see what is the logical operator and how we use them in Python programming.

Logical Operators:

There are basically three types of logical operators as below:

  • And
  • Or
  • Not

There above operators semantics or meaning is the same as their meaning in English.  To demonstrate we can take one simple example like a number can be greater then 2 and less then 5, so if we consider this example then we know that there could be only two possible numbers comes in between i.e. 3 and 4. So now let us run this example and verify the results by passing different numbers……….continue reading from my original article that was published on Experts-Exchange.com and provides a basic explanation on how to use Logical Operators in Python programming language.

 

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Boolean in Python

In this article, we will see how to use Boolean in Python where it can be used to take any decisions if it is true or false, yes or no,  1 or 0.

What is Boolean?

It is just an integer that can have 1, 0,-1 or floats like 0.32, 3.32 etc. The output of the decisions can be true or false.

In many cases during writing code or implementing any comparison to consider any decision we usually use boolean in any programming language.  ………continue reading from my original article that was published on Experts-Exchange.com and provides a basic explanation on how to use Boolean in Python programming language.

 

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Using Break condition in Python

This article will show a step by step guide on how use Break in Python

In many use cases, or due to certain business rules, we need to exit a loop in any programming language. Or, we need to stop a particular loop and continue with the rest of the code.

This article will help you to understand the use of a break in Python which is mainly used to exit the loops in python programming.

To achieve this we need to use “break” keyword while running a loop.

Let’s take a very simple example of how to use a break in Python programming language.

Example :

j= 1
for i in range (6):
j=j*2
print ('i= ',i, 'j=',j)
if j==32:
break

Open in new window

If we execute the above code in any Python interpreter, then we will see the following result :

So let’s try to understand the code without adding the break statement.

j= 1
for i in range (6):
j=j*2
print ('i= ',i, 'j=',j)
 

Open in new window

Now we see the code execute without any break. The loop starts from 1 till 5 as we have provided the range as “6” so starting from 0 till 5 the loop will execute :

If we compare the code with a break keyword, we have given an argument as if the result of “j” will be 32, then end the execution of the code so that the loop will run till range 5, as at this point the result of “j” is “32”.

This article was to explain and show you a simple programme on how to use a break statement in Python. I hope I’ve achieved just that.

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )