question

KrishnaRaoKandala-9898 avatar image
0 Votes"
KrishnaRaoKandala-9898 asked ·

Find the 10 most recent records for each Measuring Point

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


sql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ·

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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your warning!
I posted in this forum as I was not getting a response on the SAP Forum.
I am glad to inform you that your code has worked and I am getting the desired result.
Thanks again.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @KrishnaRaoKandala-9898,

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://docs.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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.