Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
ORDER BY with an aggregate field causes errorneous result |
Wed, Feb 15 2006 2:54 AM | Permanent Link |
"Kunikazu Okada" | 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 ___________________________________________/// |
Wed, Feb 15 2006 11:10 AM | Permanent Link |
"Donat Hebert \(WSI\)" | 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 ___________________________________________/// |
Wed, Feb 15 2006 11:39 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sun, Feb 19 2006 10:49 PM | Permanent Link |
"Kunikazu Okada" | "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. |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |