On Fri, 3 Jul 2015 12:15:31 +0000 (UTC), "Vishal Tiwari [email protected] [firebird-support]" <[email protected]> wrote: > Hi All, > I have below SQL, which is concatenating the ShortCode column data, but > without ordering as per the ORDER_NUMBER column in ABC table in "WITH" > clause. > Please help. > > 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_KEYwhere XYZ.FK_KEY = > '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Order By > ABC.ORDER_NUMBER) > > SELECT LIST(Distinct(TBL_SHORT_CODE.SHORT_CODE), '' ), ABC.FK_BOMFrom > ABC Join XYZ ON ABC.FK_KEY = XYZ.PK_KEY Join TBL_SHORT_CODE On > TBL_SHORT_CODE.FK_KEY = ABC.FK_KEYwhere ABC.FK_BOM = > '{009DA0F8-51EE-4207-86A6-7E18F96B983A}' And ABC.STATUS_CODE = 1Group By > ABC.FK_BOM
It doesn't work because of your use of distinct in LIST. This forces a sort which overrides any previous order. I don't think there is a solution to this problem except ineffecient and convoluted double querying. Mark
