In this post I discuss a workaround if you need a TOP N / UP TO N rows / ORDER BY in a CDS-based modeling. Unfortunately these clauses are missing in CDS views and, therefore can only be implemented by CDS Table Functions. Hopefully, it is temporary 😉

The solution is as follows: CDS Table Function calls a method in an ABAP class, this method executes a SQLscript on HANA DB and returns the data back to the CDS. From that point on, you can use it in further modeling, i.e. other CDS views, filling derived parameters, etc. The binding parts are highlighted in the same color on both CDS and CLASS sides. Also, important sections, specific to AMDP are commented respectively.

CDS Table function	Class and Method for AMDP

Procedure:

1.      Create a CDS table function with expected output definition, pointing to a dummy class & dummy method.

@EndUserText.label: 'Budget Versions'
define table function ZWG_I_BUDGETVERSIONS
with parameters 
    @Environment.systemField: #CLIENT 
    clnt            : abap.clnt
    returns {
      rclnt         : abap.clnt;
      ryear         : fis_ryear;
      rzzversion    : GENFM_EXTBR_DEFAULT_VER;
  }
implemented by method class_name=>method_name;

2.      Create a proper class and method. Code example attached

class ZCL_AMDP_FOR_REPORTING definition
  public
  final
  create public .

public section.
* this INTERFACES part is needed for AMDP to work
    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS ZM_GET_LAST2_BUDGET_VERSIONS
* the following line is the binding to my CDS, so I do not need to define any types
    FOR TABLE FUNCTION ZWG_I_BUDGETVERSIONS.
private section.
ENDCLASS.

CLASS ZCL_AMDP_FOR_REPORTING IMPLEMENTATION.

  METHOD ZM_GET_LAST2_BUDGET_VERSIONS
* this section is mandatory for HANA-AMDP's to work
    BY DATABASE FUNCTION
    FOR HDB LANGUAGE SQLSCRIPT
    OPTIONS READ-ONLY
* end of the HANA-specific section
    USING ZZWGXA.
      RETURN
-- this part is an SQL script executed on HANA
            select top 2 distinct rclnt, ryear, rzzversion
            from ZZWGXA
            order by concat(ryear, rzzversion) desc;
-- end of the HANA script
     ENDMETHOD.

ENDCLASS.

3.      Finalize the CDS table function to point to the right class & method. Final code example attached

// This CDS Table Function is needed because a CDS view
// has no possibility to select top N records and ORDER BY
// so we do it in a respective AMDP function

@EndUserText.label: 'Budget Versions'
define table function ZWG_I_BUDGETVERSIONS
with parameters 
    @Environment.systemField: #CLIENT 
    clnt            : abap.clnt
    returns {
      rclnt         : abap.clnt;
      ryear         : fis_ryear;
      rzzversion    : GENFM_EXTBR_DEFAULT_VER;
  }
// the next section is a binding to CLASS and a METHOD for AMDP
implemented by method ZCL_AMDP_FOR_REPORTING=>ZM_GET_LAST2_BUDGET_VERSIONS;

This solution has been inspired by:

SAP training S4D430 Building Views in Core Data Services ABAP (CDS ABAP), Unit 5

This help page https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abenamdp_functions_abexa.htm

Examples provided in the package SABAPDEMOS, namely class cl_demo_amdp_functions and CDS DEMO_CDS_GET_SCARR_SPFLI