Problem in Select Query( Aggregate MAX)

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Harsh_Bansal]

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

[Former Member]

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

[Harsh_Bansal]

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

[Former Member]

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.

[Harsh_Bansal]

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

[Former Member]

Hi,

Ya it worked Thanks Harsh.

Aswath.

[Harsh_Bansal]

No Problem