Sorting issue with STRING_AGG in Hana Table function

[tapas_pradhan]

hello Experts,

My requirement is to Aggregate the values in a sorted manner as below.

currently the code is as below.

FUNCTION .cs.I072-SB_Reporting.BUSINESSLOGIC.TABLE_FUNCTION::TF_SB_EFF_MSN_CONCAT" ( )
RETURNS TABLE(
"SB_NUMBER" NVARCHAR(10),
"MSN_LIST" NVARCHAR(5000),
"MSN_COUNT" INTEGER
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/*********************************
Merge all active status in single cell
 **********************************/

 return SELECT "SB_NUMBER",
   STRING_AGG("MSN",',') AS "MSN_LIST" ,
   COUNT("MSN") AS "MSN_COUNT"
FROM
(
SELECT DISTINCT
"SB_NUMBER",
CONCAT('X', lpad("MSN",5,'0')) AS "MSN"
FROM "INTERNAL_1S79"."class-Reporting.TABLEDEF::EFF.ZT_EFFECT_MSN_SB" as "ZT_EFFECT_MSN_SB"
LEFT OUTER JOIN "_SYS_BIC"."class-Reporting.BASE/CV_XCS_ZDCI0T_SB_REVI" as "CV_XCS_ZDCI0T_SB_REVI"
ON "ZT_EFFECT_MSN_SB".SB_NUMBER = "CV_XCS_ZDCI0T_SB_REVI"."SB_NUM"
WHERE ( "MSN" NOT IN ('425', '441', '434', '444', '482', '484', '522', '523') AND "CV_XCS_ZDCI0T_SB_REVI"."AC_TYPE" = '2')
OR "CV_XCS_ZDCI0T_SB_REVI"."AC_TYPE" <> '2'
order by "MSN", "SB_NUMBER"
)

group by "SB_NUMBER"  ;
END;

But I am getting results as below. The string aggregation is not in sorted manner

I want to implement similar logic which is below in console but I am getting error while using Order  by with STRING_AGG function.

Here I am getting correct values but while trying to implement below code in Table function I am getting error as "Syntax error. "order" is incorrect or misplaced."

Please help me how can I achieve above results in Table functions.

[Sandra_Rossi]

Use

order by CONCAT('X', lpad("MSN",5,'0')), "SB_NUMBER"

instead of

order by "MSN", "SB_NUMBER"