Hello.

#problem:

select Key, AVG(Data) X from Table1 group by Key
order by X DESC

returns errorneous result.


#detailed description:

With a table like this: (without any index)

strKey , floatData
------  --------
A        , 1
A        , 2
B        , 2
B        , 3

select strKey, AVG(floatData) X from Table1 group by strKey;

returns
------ ----
A        , 1.5      = (1+2) / 2
B        , 2.5      = (2+3) / 2

but,

select strKey, AVG(floatData) X from Table1 group by strKey
order by X DESC

returns
------ ----
A        , 3          = (1+2) / 1
B        , 1.25      = (2+3) / 4

Our customer reported it happend on a
DBISAM 4.22 build 5 based system.
and I suggested them (as a DBISAM reseller)
to use in-memory temporary table for workaround.

Is this a bug ?
Or should we always avoid ordering with aggregate field ?

Thanks.

/// Kunikazu Okada ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tokyo, Japan ___________________________________________///

Tim, just to add to this, it appears to only occur when invoking the DESC
index.
Added four 'C' values of .9, .8, .7, .6 ..  worked fine without descending
....  Donat.

"Kunikazu Okada" <okd@teppi.com> wrote in message
news:BA913589-1A5D-4EE9-8B1F-371AB5AEDD49@news.elevatesoft.com...
> Hello.
>
> #problem:
>
> select Key, AVG(Data) X from Table1 group by Key
> order by X DESC
>
> returns errorneous result.
>
>
> #detailed description:
>
> With a table like this: (without any index)
>
> strKey , floatData
> ------  --------
> A        , 1
> A        , 2
> B        , 2
> B        , 3
>
> select strKey, AVG(floatData) X from Table1 group by strKey;
>
> returns
> ------ ----
> A        , 1.5      = (1+2) / 2
> B        , 2.5      = (2+3) / 2
>
> but,
>
> select strKey, AVG(floatData) X from Table1 group by strKey
> order by X DESC
>
> returns
> ------ ----
> A        , 3          = (1+2) / 1
> B        , 1.25      = (2+3) / 4
>
> Our customer reported it happend on a
> DBISAM 4.22 build 5 based system.
> and I suggested them (as a DBISAM reseller)
> to use in-memory temporary table for workaround.
>
> Is this a bug ?
> Or should we always avoid ordering with aggregate field ?
>
> Thanks.
>
> /// Kunikazu Okada ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Tokyo, Japan ___________________________________________///

Kunikazu,

<< select strKey, AVG(floatData) X from Table1 group by strKey
order by X DESC  >>

It's probably a bug.  I will make sure that it's fixed in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]"
> << select strKey, AVG(floatData) X from Table1 group by strKey
>  order by X DESC  >>
>
> It's probably a bug.  I will make sure that it's fixed in the next build.

Thank you.