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

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:

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.