About ROW_NUMBER(), RANK(), and DENSE_RANK() Functions in SAP HANA

[former_member181964]

This article tells about different Rank functions in SAP HANA and how to use them based on situation.

For a given problem we can find different solutions and alternative methods. i.e. Problem is one but we have different approaches to resolve the problem. If we have similar kind of a solutions in our hand for a given problem, we need to choose the best and accurate choice.

In this blog, I'm going to explain how to choose the correct RANK function based on Business need..

We have three different types of RANK functions like ROW_NUMBER(), RANK() and DENSE_RANK(), see below for more details about these functions.

ROW_NUMBER()

The output of the this function can be non-deterministic among tie values. The ordering of the sequence is determined by the <windows_order_by_clause> within the OVER windows clause

RANK()

This function returns duplicate values in the ranking sequence when there are ties between values and the next rankings are skipped.

DENSE_RANK()

This function is used to give rank based on measure/measures. This function is same as the RANK function, but the rank number will not skip when ties are found.

See the simple example with small data set.

Eg: We have a MARKS table like below:

 

The below section is divided into three different parts/scenarios with sample data and examples.

 

Scenario 1:

Now apply functions ROW_NUMBER(), RANK() and DENSE_RANK() on above table.

Write below Code in HANA Studio in SQL Console. The below code gives first priority for Maths marks, second priority for Physics, and third priority for Chemistry.

Execute the above code and see the Results.

ROW_NUMBER() is generated numbers from 1 to 10 and allocated in sequence.

RANK() is generated Rank numbers but it skipped if students scored same marks. Students SCOTT and ADAM got same marks so for both it allocated same Rank #4. But, Rank# 5 is skipped and it allocated Rank# 6 for JAMS and JEO.

DENSE_RANK() is generated Ranks from 1 to 7. If two students scored the same marks, it allocated same Rank for both students instead of skipping Ranks.

Scenario 2:

Removing the priority on individual subjects and using the same functions on TOTAL marks, see the below code.

The result of above code is:

Scenario 3:

Apply ROW_NUMBER () function using PARTITION BY Clause.

We have PRODUCTS table with fields like Products, Color and List Price. Apply ROW_NUMBER based on COLOR (Partitioning Products based on Color) and List Price.

Note: In above code I used ORDER BY 1 ASC, it means the data will be sorted based on First Column i.e. COLOR.

At the end of this blog, I conclude that, choose the best Function based on your business needs. More or less these functions looks like similar but they give different results based on data.