Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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 Member
replied

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

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question