Select TOP N / UP TO N rows / ORDER BY in a ABAP CDS
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
Thanks for sharing. Funny coincidence: I’m actual visiting SAP training S4D430
Best regards
Michael
Â
Nice! I sometimes teach that course, too. Hope you enjoyed it.
Hi
Instead of top 2 entries, I need to pass input parameter ( TOP N)  from cds view based on the selection prompt values, please suggest any idea
CDS lacks many statements, TOP n, subquery, select single, limit. Its really ODD to write a class, attaching the same to CDS function table just to get a single record. Why it cant just support LIMIT or SINGLE statement?
If it requires to write Open SQL in a class, then why not to drop cds and write open sql in odata data provider class? Much simple, isnt it?
well, CDS offers quite more: push-down of execution to HANA, DCL Access Controls down at DB, tons of flex without creating big internal tables, flexibility of layered modeling, etc. So, I take CDS as much as I can, but make your choice.
Hi
In CDS does the ORDER BY work??? In table function output, the sorting works but when this is consumed in CDS it isn't working. Please suggest.
Hi Praseeda Aramadaka ,
not sure if your question still remains, no, there is no ORDER BY available in CDS, as the 'philosophy' behind it is that the respective consumer should decide how the data should be sorted.
However, to provide a default sorting, you can use @UI.presentationVariant in your Consumption View (where generally you put all the UI-Annotations). You need something like this before the DEFINE VIEW statement:
@UI.presentationVariant: [{
    sortOrder: [{ by: 'BeginDate', direction: #ASC }]
}]
define view <MyView>... blablabla...
Hope it helps and best regards,
Marc