How to use order by in Query %DLIST ?
Hello My Friends,
I have a question how to use order by %DLIST, this is my code:
SELECT
$ListToString(%DLIST(DISTINCT MRDIA_ICDCode_DR->MRCID_Code),', ' ) ICDX,
$ListToString(%DLIST(DISTINCT (MRDIA_ICDCode_DR->MRCID_Desc || ' (' || MRDIA_DiagnosisType_DR->DTYP_Code || ')')),', ' ) Diagnose
FROM SQLUser.PA_Adm
LEFT JOIN SQLUser.PA_AdmInsurance ON (PAADM_RowID = INS_ParRef AND INS_Rank = 1)
LEFT JOIN SQLUser.PA_AdmPackage ON (PAADM_RowID = PACK_ParRef)
LEFT JOIN SQLUser.MR_Adm on MRADM_ADM_DR = PAADM_RowID
LEFT JOIN SQLUser.MR_Diagnos ON MRADM_RowId = MRDIA_MRADM_ParRef
LEFT JOIN SQLUser.OE_Order ON PAADM_RowID = OEORD_Adm_DR
LEFT JOIN SQLUser.OE_OrdItem AS OD1 ON OEORD_RowId = OEORI_OEORD_ParRef AND OEORI_Categ_DR = 20 AND OEORI_ItemStat_DR <> 1
LEFT JOIN SQLUser.OE_OrdItem AS OD2 ON OEORD_RowId = OD2.OEORI_OEORD_ParRef AND OD2.OEORI_Categ_DR = 11 AND OD2.OEORI_ItemStat_DR <> 1
WHERE
PAADM_ADMNo IS NOT NULL AND PAADM_Type = 'E'
AND PAADM_AdmDate >= '2024-11-01' AND PAADM_AdmDate <= '2024-11-25'
GROUP BY PAADM_RowId
and this is the result
.png)
and What I need is how to order by the Diagnose Colum like (M) for the first and (S) for second and (C008) for the last ?
like this one
.png)
Does anyone could help me ?
Thank You
Best Regards,
Steve Henry
Comments
You cannot use ORDER BY inside %DLIST based on the available syntax. %DLIST is documented as:
%DLIST([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING]) [1][2]
So %DLIST supports DISTINCT and DISTINCT BY(col-list), but the retrieved material does not show any syntax for ordering the elements inside the list result itself. [1][2]
A regular ORDER BY can sort the rows returned by the query, for example after GROUP BY, but that affects the result set rows, not the internal order of values concatenated inside %DLIST. [2]
Because of that, sorting the values within your Diagnose column so that (M) comes first, (S) second, and (C008) last is not described in the retrieved documentation. [1][2]
Sources: