topic Re: Problem in Select Query( Aggregate MAX) in Application Development and Automation Discussions

Problem in Select Query( Aggregate MAX)

Former Member — Fri, 17 Feb 2012 10:05:43 GMT

Hi all,

I am getting a problem in selecting records.

suppose the data is like this

version start_date end_date

1 01.01.2012 31.01.2012

2 01.01.2012 31.01.2012

3 01.01.2012 31.01.2012

4 01.01.2012 31.01.2012

1 01.02.2012 31.02.2012

2 01.02.2012 31.02.2012

I need to select rows which should contain Max of version for the same start_date and end_date along with the date.

for this I am writing select query which is not working


select MAX( version ) sub_start_date sub_end_date
  from zsome
  into CORRESPONDING FIELDS OF TABLE it_some
  GROUP BY version sub_start_date sub_end_date.

What might be the problem?

Note: version is a key field.

Please help.

Thanks in advance.

Aswath.

Edited by: aswathsa on Feb 17, 2012 3:36 PM

Re: Problem in Select Query( Aggregate MAX)

Former Member — Fri, 17 Feb 2012 10:19:17 GMT

Hi,

Try this.

select MAX( version ) sub_start_date sub_end_date
  from zsome
  into CORRESPONDING FIELDS OF TABLE it_some
  GROUP BY sub_start_date sub_end_date.

Thanks,

iostreamax

Re: Problem in Select Query( Aggregate MAX)

Former Member — Fri, 17 Feb 2012 10:19:34 GMT

Hi,

Compute MAX(version) and put it in a local variable.

Then query the table with version = local variable.

Since you are using aggragate function , do that aggragation first and go for group by.

Ben.

Re: Problem in Select Query( Aggregate MAX)

Harsh_Bansal — Fri, 17 Feb 2012 10:30:31 GMT

Hi,

What is the problem which you are facing??

It will not assign max version into the internal table by the way you mentioned. Is this the problem??

Regards,

Harsh Bansal

Re: Problem in Select Query( Aggregate MAX)

Former Member — Fri, 17 Feb 2012 10:38:18 GMT

Hi,

For the above mentioned data my output should be like this

4 01.01.2012 31.01.2012

2 01.02.2012 31.02.2012

But the query i have written is unable to give this answer.

Hope the question is clear.

Thanks.

Aswath

Re: Problem in Select Query( Aggregate MAX)

Harsh_Bansal — Fri, 17 Feb 2012 11:01:05 GMT

Hi,

Use this approach -


SELECT version
       sub_start_date
       sub_end_date
  FROM zsome
  INTO CORRESPONDING FIELDS OF TABLE it_some.
SORT it_some DESCENDING BY version sub_start_date sub_end_date.
DELETE ADJACENT DUPLICATES FROM it_some COMPARING sub_start_date sub_end_date.

It will give you the desired result.

Regards,

Harsh Bansal

Re: Problem in Select Query( Aggregate MAX)

Former Member — Fri, 17 Feb 2012 11:06:15 GMT

Hi,

I know that solution.

what I am trying to do is selecting only the required records at a time. Not selecting all and deleting the duplicates.

Is there any way for selecting at a time?

Thanks.

Aswath.

Re: Problem in Select Query( Aggregate MAX)

Harsh_Bansal — Fri, 17 Feb 2012 11:19:15 GMT

Okay..here's your solution -

2 points -

1. Instead of using corresponding fields, declare types with your three fields, then select into table of that type.

2. In select statement, use group by for your dates only...remove version from it.

You will get the desired solution.

Let me know if there is any issue.

Regards,

Harsh Bansal

Re: Problem in Select Query( Aggregate MAX)

Former Member — Fri, 17 Feb 2012 12:26:25 GMT

Hi,

Ya it worked Thanks Harsh.

Aswath.

Re: Problem in Select Query( Aggregate MAX)

Harsh_Bansal — Fri, 17 Feb 2012 12:31:27 GMT

No Problem