topic select with aggregate function MAX in Application Development and Automation Discussions

select with aggregate function MAX

Former Member — Tue, 06 Oct 2009 06:32:59 GMT

Hi,

Can I write a query like:

select field1 field2 MAX( field3 )

from z_table

into table itab

for all entries in itab2

where field1 = itab2-field1

and field2 = itab2-field2.

In the above case all the 3 fields are key fields.

Thanks in advance.

Re: select with aggregate function MAX

Former Member — Tue, 06 Oct 2009 06:38:15 GMT

Why dont you try to execute it and check if its working?

Re: select with aggregate function MAX

Former Member — Tue, 06 Oct 2009 06:40:11 GMT

try this

select , max() as field2, min() as field3, ...

from........

Re: select with aggregate function MAX

Former Member — Tue, 06 Oct 2009 06:43:09 GMT

Hi,

I did try it.. but got an error like: "The field "z_table~field2" from the select list is missing in the GROUP BY clause" but my aggregate funtion is used only for the field3..... So, I wanted to know if anybody has come across such a query....

Thanks,

Re: select with aggregate function MAX

rainer_hbenthal — Tue, 06 Oct 2009 06:46:57 GMT

If there is only one max function in the select you do not need a group by. In your case, you need one. But this was told you by the compiler....

Re: select with aggregate function MAX

ThomasZloch — Tue, 06 Oct 2009 06:48:34 GMT

Check online help for aggregate functions. All selected columns which are not used as aggregate functions must appear in the GROUP BY clause.

Thomas

Re: select with aggregate function MAX

Former Member — Tue, 06 Oct 2009 07:07:56 GMT

Hi All,

The code works fine after the group by clause.

Thanks for your help.

Thanks,

Sindhu