Selecting MAX over multiple dependent fields

Question

Here's a sample of the data I wish to select from in the AFRU table.

+----------------------+-----------------+-------------------------+
| Confirmation (RUECK) | Counter (RMZHL) | Finish Execution (IEDD) |
+----------------------+-----------------+-------------------------+
|                30116 |               1 | 08/26/2014              |
|                30116 |               2 | 08/27/2014              |
|                30116 |               3 | 09/27/2013              |
+----------------------+-----------------+-------------------------+

Given this data set, I'd like to select the last row because it contains highest RMZHL value (I'm really only after the IEDD value of that record). Here's the code I've been trying to use:

SELECT MAX( rmzhl ), iedd FROM afru
                          INTO (@DATA(lv_rmzhl), @DATA(lv_iedd))
                          WHERE rueck = '0000030116'
      GROUP BY rmzhl, iedd.
      ...
      <some fancy code here>
      ...
ENDSELECT.

However, this selection always nets me the values 1 for lv_rmzhl, and 20140826 for lv_iedd, therefore not getting the MAX value like I'm attempting to get. I understand that his could be easily accomplished by putting it all into an internal table and sorting on the RMZHL field, but I'm looking for a more proper way.

Do you just need the max value of RMZHL for each RUECK or do you need additional payload data (like IEDD)? — vwegert
@vwegert technically, all I need is the IEDD value 20130927. — gkubed

Unknown Unknown · Accepted Answer · 2016-11-04T17:53:03

You could do a subselect like:

SELECT SINGLE iedd
  FROM afru
  INTO @DATA(lv_iedd)
  WHERE rueck = '0000030116'
    AND rmzhl = ( SELECT MAX( rmzhl ) FROM afru WHERE rueck = '0000030116' ).

I've used this in a lot of cases even in combination with FOR ALL ENTRIES and the performance difference is very low.

Selecting MAX over multiple dependent fields

1 Answers