SAP HANA SQL QUERY TO FETCH LATEST RECORD FROM EACH DOCUMENT TYPE

[govardan_raj]

Hi ,

I have a INVOICE_TABLE, in which we have 30 columns, in which one column named Document_type has distinct 6 values a doc_a, doc_b,.......doc_e & doc_f.
there are lakhs of records in total.

Now i have a requirement to fetch the recently created record for each document type so finally my query result should show all columns and has only 6 rows as we have 6 distinct document types

 SELECT * FROM  INVOICE_TABLE   WHERE INVOICE_NUMBER IN
(
SELECT  DISTINCT INVOICE_NUMBER  FROM INVOICE_TABLE   WHERE  DOCUMENT_TYPE IN ('DOC_A','DOC_B','DOC_C','DOC_D','DOC_E','DOC_F')  
   GROUP BY DOCUMENT_TYPE,INVOICE_NUMBER,CREATION_DATE  ORDER BY CREATION_DATE DESC,DOCUMENT_TYPE DESC LIMIT 6
)

Am using above query, but unfortunately it is fetching only 6 records and all 6 are doc_a type record.

Kindly do the need full.

[pfefferf · Accepted solution]

One option is to use window function ROW_NUMBER like following. With that you get the latest data record (based on creation_date) per document type:

select * from (
  select row_number() over (partition by document_type order by creation_date desc) as row_num, *
  from INVOICE_TABLE
  where document_type in ('DOC_A', 'DOC_B', 'DOC_C', 'DOC_D', 'DOC_E', 'DOC_F')
) where row_num = 1;

[govardan_raj · Accepted solution]

Thanks a lot , it is working.

Regards

Govardan.

[IvanMiguel · Accepted solution]

Thanks for sharing, Florian.

I've been looking for solutions to make this query work efficiently as well.

Regards,

Ivan