Rank and Dense Rank functions in OBIEE & How to implement Rank and Dense Rank functions in OBIEE .How to write SQL query of Rank and Dense Rank functions in database?

If in a class of 5 students, they conducted an exam and 2 got 95, other 2 got 93 and the last one got 91. So, if we are using the Rank function, the output will be as shown below.

Students	Marks	Rank
student 1	95	1
student 2	95	1
student 3	93	3
student 4	93	3
student 5	91	5

And if we make use of the Dense Rank Function, the output will be as shown below.

Students	Marks	Rank
student 1	95	1
student 2	95	1
student 3	93	2
student 4	93	2
student 5	91	3

Hence, we can make sure that Dense Rank function doesn’t skip the values and provides a continuous series of numbers as compared to Rank function.

Now, we can see how to write the query for the same on the database.

SQL Query of Rank function in Database 

SELECT Student,Marks,Rank() over (PARTITION BY Marks order by student) as Rank  from Class 

SQL Query of Dense Rank function in Database 

SELECT Student,Marks,Dense_Rank() over (PARTITION BY Marks order by student) as Dense_Rank  from Class 

Now, lets understand how to implement the same in OBIEE.

 Rank in OBIEE

OBIEE has an internal function called Rank and can be used directly for this. We can use the below syntax in the fx of the column:

RANK(“class”.”student” by “class”.”marks”)

  

Dense Rank in OBIEE

Since, Dense Rank is not an internal function of OBIEE we need to make use of the Evaluate function to use this.Evaluate function is used when we have to make use of the database functions in OBIEE.We can use the below syntax in the “fx” of the column

EVALUATE(‘DENSE_RANK() OVER (PARTITION BY %2 ORDER BY %1)’AS INTEGER,”class”.”student”, ”class”.”marks”) 

The above two syntax shows us that how we can make use of the Rank and Dense Rank functions in OBIEE.

Points to be noted:

We can make use of Rank function as filter in a report but we can’t use Dense Rank function as filter in a report. This is because Rank is an internal function to OBIEE.Therefore OBIEE forms a subquery to calculate the Rank and filters the data in the outer query.

Example: RANK(class.student by class.marks) <=2