| Subject | Re: [firebird-support] Order By Not Working Usin g "WITH" Clause |
|---|---|
| Author | Mark Rotteveel |
| Post date | 2015-07-03T15:16:08Z |
Ok, then there are also other grouping or sorting effects involved. Unfortunately there is no way to enforce a specific order. The CTE trick you use is just that: a trick and it doesn't always work.
So until Firebird supports a list(... Order by...), there is no way to always get a deterministic order.
Mark
----- Reply message -----
Van: "Vishal Tiwari vishualsoft@... [firebird-support]" <firebird-support@yahoogroups.com>
Aan: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Onderwerp: [firebird-support] Order By Not Working Using "WITH" Clause
Datum: vr, jul. 3, 2015 17:00
Van: "Vishal Tiwari vishualsoft@... [firebird-support]" <firebird-support@yahoogroups.com>
Aan: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Onderwerp: [firebird-support] Order By Not Working Using "WITH" Clause
Datum: vr, jul. 3, 2015 17:00
Even if I don't use Distinct, i don't get expected result.
On Friday, 3 July 2015 7:17 PM, "Mark Rotteveel mark@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:
On Fri, 3 Jul 2015 12:15:31 +0000 (UTC), "Vishal Tiwari
vishualsoft@... [firebird-support]"
<firebird-support@yahoogroups.com> wrote:
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
vishualsoft@... [firebird-support]"
<firebird-support@yahoogroups.com> 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