Fetch max date and other field from the same row of a table grouped by other fields in SAP HANA

You have done partially correct but just missed one step further to get the desired result. The case where after grouping you need to pick one of the rows from the results, use rank number or rank function.

You need to partition your data based on your requirement, then order them as per your requirement again then pick the desired row.

Example

For e.g. partition your data by Item Number and Shop Id. Then order them in descending order of Date column. Then pick the row with row number as 1

select date, Order_Number   from
  (SELECT  *, row_number() over ( partition by Item_Number,Shop_Id order by Date desc ) rownum
      from <Table>  )   where rownum = 1;