Top N rows using views

[Former Member]

Hi,

     can we show TOP N rows using any of the views(Attribute, Analytic , Calc.) ?.

I have created a calc. view  which is a group by username. The output is something like this:

        USNAM   COUNT

         user123     10

         user121     3

         UserX12     6

         ;;;;;

         ;;;;;;;

I want only  top 5 users . Sql Query for this:'  ................order by "COUNT" desc  limit by 5'; which works fine. But I want the output of view exactly the same way. Is it possible?.

Thanks,

Mukund

[Former Member · Accepted solution]

Hi Murks,

You can do the same thing using scripted Calculation view ....and having the same SQL query for varout

Let us know for further information

Regards,

MADhu

[Former Member · Accepted solution]

Thankyou Madhu for you reply.  Yes I did what you said. It works as expected. But is it possible to do the same using GRAPHICAL VIEW OR CE FUNCTION in Calculation view ?. How to user Order By clause and LIMIT using CE FUNCTIONs?.

[Former Member · Accepted solution]

Hi Muks,

Tried with graphical but could not achive the same....we can have a procedure and use CE functions but for again to get  top 5  we need to  go for SQL statement which may reduce the performance ....so thought scripted is ideal one....

are you trying  to achive anything other calculations on  top5.....just curious as the scripted was working in your case

Regards,

MADhu

[Former Member · Accepted solution]

Hi Madhu, my requirement is to fetch Top 5 users which I can achieve using scripted Calculation view and using SQL query in it(with ORDER BY and LIMIT clauses ). I don't think this is possible using Graphical View or using CE functions.

Thanks,

Muks

[Former Member · Accepted solution]

if you are using SPS09 ...we have an option of Ranking in the Graphical View......it might help your case..

Regards,

MADhu

[former_member210481]

HI Muks , you can do it easily in HANA SPS09 , create a  graphical  calculated column in a CAL VIEW with a default value (eg 1) . Now you can use the rank node and partition it based on the calculated column . Give the threshold as 5 . So now you would get TOP5 (Based on your threshold value)

Thanks,

Tanush