Selecting Top n Rows for distinct column values in HANA SQL editor

[Former Member]

Hi All,

My requirement is I need to select top n values for every distinct column value.

Suppose table contains three fields - Customer_Name , Region , Sales

I need top 5 Customers for every distinct region based on sales.

Would appreciate any help in this regard.

Regards,

Nakul Kothari

[former_member182302 · Accepted solution]

Hi Nakul,

Are you asking for help to do in Graphical views or SQL?

If SQL please find the below:

SELECT TOP 5 Customer_Name , Region , sum(Sales) FROM TABLE

GROUP BY Customer_Name , Region

ORDER BY sum(Sales) DESC

Let me know if you are looking for graphical view based solution.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

Thanks for your reply.

However, your solution restricts the output to only 5 rows. It gives total 5 rows.

Whereas I need top 5 Customers for every distinct region.

Suppose  the table is like this

I want my output -Top 2 customers for every distinct region based on sales as

REGION-----CUSTOMER------SALES

C1 -------- -------P2----------- --------200

C1-------- -------P4----------- --------150

C2 -------- -------P2----------- --------300

C2-------- -------P1----------- --------200

If possible, you can also provide graphical solution

[former_member182302 · Accepted solution]

Hi Nakul,

Understood your requirement now. We can use window functions to achieve what you were referring to as mentioned below:

SELECT Region_cd,Country,SUM(Units) as Units

FROM

(

SELECT Region_cd,Country,sum(Units) AS Units,rank() OVER (PARTITION BY Region_cd ORDER BY SUM(Units) DESC ) AS Rank

FROM "TABLE/VIEW"

GROUP BY Region,Country

)

WHERE Rank <= 5

GROUP BY Region,Country

This will give you Top 5 countries in each distinct region.

Let me know if this is addressing your problem description as expected.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Krishna,

Thanks for your reply. This is exactly what I wanted.

[former_member182302 · Accepted solution]

Hi Nakul,

Happy that it answered your question. Do mark this question as "Answered" it would be helpful.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

This message was moderated.