RE: Getting the number of rows returned by a select with group by

Hi Jonathan. >I trust you mean 7.31 or later -- you should not be using 7.30 any >more; nor, indeed, should you be using anything less than 7.31.xD7 >(released in January 2004). Anything earlier is not good news. Yes sorry, I meant 7.31.x or above. However we do have lots of installs of 7.31.TD1-5 which are working just fine on NT4/W2k and Win2003. Why do we need v7.31.xD7 particularly? >Do you mean that the solution must work in 7.31 and with 9.x, or is a >solution that works only in 9.x permitted? We don't have any 9.x installs yet unfortunately. When we tested 9.x our apps ran quite a lot slower on indentical hardware to v7.31 (sometimes up to 60%!), so we've avoided it to date. That was on 9.30.x though so we do need to find the time to retest the latest v9.4.TDx release to see if things have improved. Thanks for the response. David -----Original Message----- From: jonathan.leffler@gmail.com [mailto:jonathan.leffler@gmail.com] Sent: 23 September 2004 13:32 To: informix-list@iiug.org Subject: Re: Getting the number of rows returned by a select with group by. David N. Heydon wrote: > I've have a quick look around and don't seem to be able to find a > solution to count the number of rows returned by queries like:- > > SELECT a,b,c,COUNT(*) FROM x > GROUP BY a,b,c > HAVING COUNT(*) > 1> > I've seen that syntax like:- > > SELECT COUNT(*) FROM (SELECT a,b,c,COUNT(*) FROM x GROUP BY a,b,c > HAVING COUNT(*) > 1) x1> > works on other databases. Is there a simple way to do this on > Informix without using a temporary table? That notation does not yet work in IDS -- it's high on my hitlist, but not there yet. > Target databases will be IDS version 7.30 or above. I trust you mean 7.31 or later -- you should not be using 7.30 any more; nor, indeed, should you be using anything less than 7.31.xD7 (released in January 2004). Anything earlier is not good news. Do you mean that the solution must work in 7.31 and with 9.x, or is a solution that works only in 9.x permitted? SELECT COUNT(*) FROM TABLE(MULTISET( SELECT a,b,c,COUNT(*) FROM x GROUP BY a,b,c HAVING COUNT(*) > 1 )) This won't work in 7.31. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> "I don't suffer from insanity - I enjoy every minute of it" sending to informix-list

David N. Heydon wrote: >Jonathan Leffler wrote: >> David N. Heydon wrote: >>>Target databases will be IDS version 7.30 or above. >>I trust you mean 7.31 or later -- you should not be using 7.30 any >>more; nor, indeed, should you be using anything less than 7.31.xD7 >>(released in January 2004). Anything earlier is not good news. > > Yes sorry, I meant 7.31.x or above. However we do have lots of installs of > 7.31.TD1-5 which are working just fine on NT4/W2k and Win2003. Why do we > need v7.31.xD7 particularly? On Windows, the update is much less critical - in fact, you can probably stay put without coming to too much grief. If you were using Unix, there are sound reasons for upgrading that were detailed sufficiently well in the alert sent out when the versions were released, back in January 2004. Similar comments apply to XPS earlier than 8.40.xC1, IDS 9.30 earlier than 9.30.xC7, or IDS 9.40 earlier than 9.40.xC3. Note that IDS 9.2x does not figure in this list - you need to upgrade from 9.2x - preferably to 9.40. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/