Find the 10 most recent records for each Measuring Point

Question

Hi All,
I am working on the SQL code for SAP HANA and we have the following sample code for a Table:

SELECT
     "MEASURINGDOC",
     "CHARECTERISTICNAME",
     "UNITOFMEASURE",
     "READ_DATE",
     "MEASURING_READ",
     "MESURING_POINT",
     "CREATEDON" 
FROM "_SYS_BIC"."ZPSE.SDASAPRAW/CV_BW_SAP_ZPM_O61_MEASURING_DOCUMENTS"

For each MESURING_POINT, there are several MEASURINGDOC records created over a period of time. Some have a frequency in Weeks, some are Monthly, some are Quarterly etc. Each MEASURINGDOC has the CREATEDON date.
I want to extract the 10 most recent records for each selected MESURING_POINT.
There are over 17 million records in the Table for MESURING_POINT in thousands.
I will appreciated any help from the forum.

Krishna

Answer 1

SAP HANA? You are in a forum for Microsoft products, and the specif tag you have used is for Transact-SQL which is the SQL dialect used in Microsoft SQL Server, a product completely unrelated to SAP HANA.

This query is ANSI-compliant so it may run on SAP HANA:

WITH CTE AS (
     SELECT
          "MEASURINGDOC",
          "CHARECTERISTICNAME",
          "UNITOFMEASURE",
          "READ_DATE",
          "MEASURING_READ",
          "MESURING_POINT",
          "CREATEDON",
          row_number() OVER(PARTITION BY MESURING_POINT ORDER BY READ_DATE DESC) AS rowno
     FROM "_SYS_BIC"."ZPSE.SDASAPRAW/CV_BW_SAP_ZPM_O61_MEASURING_DOCUMENTS"
)
SELECT   "MEASURINGDOC",
          "CHARECTERISTICNAME",
          "UNITOFMEASURE",
          "READ_DATE",
          "MEASURING_READ",
          "MESURING_POINT",
          "CREATEDON"
FROM   CTE
WHERE rowno = 10

But if it explodes in your face or is way to slow, you will need to google around to find a forum where they discuss SAP HANA.

Answer 2

Hi @Krishna Rao Kandala ,

Welcome to Microsoft Q&A!

SAP HANA SQL is currently not supported in the Q&A forums, the supported products are listed over here https://learn.microsoft.com/en-us/answers/products.

You could post your SAP HANA SQL question on related forums after some searching on Google.

According to your requirement, we will use ROW_NUMBER in our SQL Server which is also working in SAP HANA SQL.

ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

You could refer Erland's query or below:

SELECT * FROM  
(  
SELECT  
      "MEASURINGDOC",  
      "CHARECTERISTICNAME",  
      "UNITOFMEASURE",  
      "READ_DATE",  
      "MEASURING_READ",  
      "MESURING_POINT",  
      "CREATEDON" ,  
	  ROW_NUMBER() OVER (PARTITION BY "MESURING_POINT" ORDER BY "CREATEDON" DESC) AS "ROW_ID"  
 FROM "_SYS_BIC"."ZPSE.SDASAPRAW/CV_BW_SAP_ZPM_O61_MEASURING_DOCUMENTS") AS A  
 WHERE "ROW_ID"<=10  

You could add or remove the double quotes "" if necessary.

If you have any further issue about T-SQL, welcome to post another question here.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.