RE: Whatcha' wanta have?????

Hi, One thing I would like to see that has bugged me on and off over the years is to do with the 'group by' clause in SQL (assuming it has not already been dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this year). When doing selects with a 'group by', everything in your select list (maybe entire records with many columns) has to be listed in your 'group by' clause, even though you only want to group by one or two of the selected columns. This is most frustrating. So, can we have 'group by' where you only need list the columns you actually want to group by while selecting many more columns than listed in the group by (or does this break some SQL standard or something?). Regards, Bryce Stenberg. DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm sending to informix-list sending to informix-list

Bryce Stenberg wrote: > Hi, > > One thing I would like to see that has bugged me on and off over the years > is to do with the 'group by' clause in SQL (assuming it has not already been > dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this > year). > > When doing selects with a 'group by', everything in your select list (maybe > entire records with many columns) has to be listed in your 'group by' > clause, even though you only want to group by one or two of the selected > columns. This is most frustrating. > > So, can we have 'group by' where you only need list the columns you actually > want to group by while selecting many more columns than listed in the group > by (or does this break some SQL standard or something?). > > Regards, > Bryce Stenberg. > > > DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm > > sending to informix-list > > > sending to informix-list Bryce, Can you illustrate this with an example and intended semantics? The rule is that any local element in the select list which is not aggregated needs to be grouped. Thus it needs to be in the GROUP BY clause. If you name, say, a column which is neither grouped upon nor aggregated, what do you expect as a result? Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab