Order By is not working using "WITH" Clause

Question

I am using Firebird database.

I have the SQL below, which is concatenating the SHORT_CODE column data, but without ordering as per the ORDER_NUMBER column in ABC table in WITH clause.

With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As 
(
SELECT Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY 
From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1
Order By ABC.ORDER_NUMBER
)


SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOM
From ABC 
Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
Join TBL_SHORT_CODE On TBL_SHORT_CODE.FK_KEY = ABC.FK_KEY
where ABC.FK_BOM = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1
Group By ABC.FK_BOM

Answer 1

ORDER BY does not function in a common table expression, and your join to TBL_SHORT_CODE would gain no benefit from it anyway. If your ordering is simply to order the output, then alter your script to order in your final query:

With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As 
(
SELECT XYZ.SHORT_CODE As SHORT_CODE, ABC.FK_KEY
From ABC Join XYZ On ABC.PK_KEY = XYZ.FK_KEY
where XYZ.FK_KEY = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1
GROUP BY XYZ.SHORT_CODE, ABC.FK_KEY
)


SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOM
From ABC 
Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY 
Join TBL_SHORT_CODE On TBL_SHORT_CODE.FK_KEY = ABC.FK_KEY
where ABC.FK_BOM = '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And     ABC.STATUS_CODE = 1
Group By ABC.FK_BOM