# SAP HANA: Dynamic Ranking using Script based (SQL Script) Vs Graphical Calculation view Vs Script based (CE Functions)

Hi Folks,

In this blog, i will be explaining on how to achieve “Custom” Ranking using Graphical Calculation View.

Problem Description:

We have to show Top ‘2’ ( Depends on the input from customer) Employees by “Salary” wise for each “Region” .i.e Top 2 employees in each region who are earning more salaries 🙂

**Note: if any body has the same salary they will share the same rank and they get ranked on the order of highest salary.

Example: Let us take the same table (Employee table) i have used in my previous blogs

SAP HANA: Using “Dynamic Join” in Calculation View (Graphical)

Using Script basec Calculation View using SQL Script:

We can achieve this very easily using window functions using Script based Calculation View as shown below:

SELECT “REGION”,”EMPLOYEE NAME”,”SALARY” FROM

(SELECT “REGION”,”EMPLOYEE NAME”,”SALARY”,RANK() OVER (PARTITION BY “REGION” ORDER BY SALARY DESC) AS RANK

FROM EMPLOYEE)

WHERE RANK <= 2 ( we can use input parameter to make this dynamic)

Have achieved the same intended result using Graphical Calculation View as well.Please note that we cannot use “Rank” function in the graphical calculation view ( atleast in the current version it is not supported 🙂 )

Using Graphical Calculation View:

Please find the snapshot of the model created below:

Have a look on this document for further details on creating it.

SAP HANA: Implementing RANK using Graphical calculation View

Output:

As seen above, based on the input you give ex: 2, you will get Top 2 Employees ranked based on their salaries.

Using Script based Calculation Views using CE Functions:

As it would be difficult to show all the nodes and their screenshots , i have recreated the same logic using “CE” functions as shown below:

/********* Begin Procedure Script ************/

BEGIN

t = CE_COLUMN_TABLE(KRISHNA.EMPLOYEE);

t1 = CE_PROJECTION(:t,[CE_CALC(‘1’,int) as dummy, “REGION”,”EMPLOYEE NAME”, “SALARY”]);

t2 = CE_PROJECTION(:t,[CE_CALC(‘1’,int) as dummy, “REGION”,”EMPLOYEE NAME” AS “EMPLOYEE NAME1”, “SALARY” as SALARY2]);

t3 = CE_JOIN(:t1, :t2, [dummy,REGION]);

t4 = CE_AGGREGATION(:t1,[sum(dummy) as “count”],[dummy,”REGION”]);

t5 = CE_PROJECTION(:t4,[“REGION”,”count”,dummy]);

t6 = CE_JOIN(:t5, :t3, [dummy,REGION],[“count”,dummy,”REGION”,”EMPLOYEE NAME”,”EMPLOYEE NAME1″,”SALARY”,”SALARY2″]);

t7 = CE_PROJECTION(:t6, [“REGION”,”EMPLOYEE NAME”,”EMPLOYEE NAME1″, “SALARY”, SALARY2,dummy,”count”],'”SALARY2″ <= “SALARY”‘);

t8 = CE_AGGREGATION(:t7,[count(“EMPLOYEE NAME”) as rank1],[“REGION”,”EMPLOYEE NAME”, “SALARY”,”count”]);

t9 = CE_PROJECTION(:t8, [“REGION”,”EMPLOYEE NAME”, “SALARY”, rank1,”count”,CE_CALC(‘”count”-“RANK1″+1’,int) as rank]);

var_out =  SELECT REGION,”EMPLOYEE NAME” AS “EMPLOYEE_NAME”,SALARY,rank as “rank” FROM :t9;

END /********* End Procedure Script ************/

Hence we saw the 3 ways in which we can get the ranking i.e Calc Script using SQL Vs Calc Graphical Vs Calc Script using CE functions.

Your’s

Krishna Tangudu 🙂

### Assigned Tags

You must be Logged on to comment or reply to a post.

Good one Krishna,

For the graphical one, you should have pasted separate screenshot of each.

Krishna Tangudu
Blog Post Author

Thank you Raj 🙂 for your feedback.

I left it because i felt it will make the blog very lengthy. Will definately add those details in the seperate blog and share it.

Regards,

Krishna Tangudu

Hi Krishna,

Very good blog, Can you paste screenshots on how you have achieved using the graphical calculation view

Regards,

S

Nice one Krishna.

Krishna Tangudu
Blog Post Author

Thank you so much Latha

Nice Blog krishna ,

Thanks for the efforts..

What happen to your old blogs , I can't see those.. if possible try to migrate those ..

Regards,

Mahesh

Krishna Tangudu
Blog Post Author

Hi Mahesh,

My other blogs are in my old account you can find them in the below link:

Lalitha Swaroop Krishna Tangudu

As i changed my employer i created a new account and It seems it is not possible to get my old account linked to the new one 🙁

Regards,

Krishna Tangudu

It'd be good to have a performance comparison on the 3 modeling options.

Could you populate the test table with some test data (100k+ records at least, 1M+ would be better) and run each model 3 times and take the average time?

Krishna Tangudu
Blog Post Author

Yes Henrique.

Will definitely check that out and see which one of the options gives the best time.

Regards,

Krishna Tangudu

Hi Krishna,