Select max field value from a group of records?

Question

I need to get the lines with the highest EXBEL registers from an itab for every different VKONT.

The result table may contain several lines for the same VKONT value if this latter one has several lines in the source table with the same highest EXBEL value.

Actual source table:

SPARTE  VKONT         EXBEL
05      800000008422  1NSN150900000058
L2      800000008422  1NSN150900000058
05      800000008422  1NSN150900000037
L2      800000008422  1NSN150900000037
05      800000008422  1NSN150900000013
L2      800000008422  1NSN150900000013
05      800000008415  1HSN151200000009
S1      800000008415  1HSN151200000009
05      800000008415  1HSN151200000008
S1      800000008415  1HSN151200000008
L1      800000008422  1NSN150900000050
L1      800000008422  1NSN150900000029
L1      800000008422  1NSN150900000023
05      800000008415  1HSN151200000012
S1      800000008415  1HSN151200000012
05      800000008422  1NSN150900000058
L2      800000008422  1NSN150900000058
05      800000008415  1HSN151200000009
S1      800000008415  1HSN151200000009

Expected result table:

SPARTE  VKONT         EXBEL
05      800000008422  1NSN150900000058
L2      800000008422  1NSN150900000058
05      800000008415  1HSN151200000012
S1      800000008415  1HSN151200000012

I tried different solutions but didn't work.

Any help will be appreciated.

Raúl.

Please edit your question to clarify the values you expect. Moreover please attach the code you have tried (relevant part) and explain what you got until now. See the [How to Ask](https://stackoverflow.com/help/how-to-ask) page. — Sandra Rossi, Mar 21 '19 at 15:57
I have edited your question to clarify it, please confirm whether it corresponds to your question. If no, please edit it. Thanks. — Sandra Rossi, Mar 23 '19 at 08:48

score 4 · Answer 1 · answered Mar 22 '19 at 01:39

4

Starting from 7.52, you can select on internal tables. Sample code as below.

TYPES:
BEGIN OF ty_s_value,
   sparte TYPE char2,
   vkont  TYPE char12,
   exbel  TYPE char16,
END OF ty_s_value.

TYPES:
ty_t_value TYPE STANDARD TABLE OF ty_s_value .

DATA:
  lt_value TYPE ty_t_value.

lt_value = VALUE #(
                    ( sparte = '05' vkont = '800008422' exbel = '0000000000000001')
                    ( sparte = 'l2' vkont = '800008422' exbel = '0000000000000002')
                    ( sparte = 'l2' vkont = '800008422' exbel = '0000000000000004')
                    ( sparte = '05' vkont = '800008423' exbel = '0000000000000003')
                    ( sparte = 'l2' vkont = '800008423' exbel = '0000000000000002')
                    ( sparte = 'l2' vkont = '800008423' exbel = '0000000000000005')
                 ).

SELECT FROM @lt_value AS a FIELDS a~sparte, a~vkont, MAX( a~exbel ) AS exbel 
   GROUP BY a~sparte, a~vkont
   ORDER BY a~sparte, a~vkont INTO TABLE @DATA(result).

answered Mar 22 '19 at 01:39

Haojie

5,665
1
15
14

Wow, this method feels super easy and intuitive. Sadly, I work with 740 SAP version. – Raúl D.Martín Mar 22 '19 at 07:44
It's not only 7.52, it also depends on the database and its release (OK for HANA 2.0, but not for Sybase ASE 16.0; it can be checked at runtime with the method USE_FEATURES of the class CL_ABAP_DBFEATURES). A performance benchmark would be interesting to know, I fear SELECT could be very slow compared to ABAP statements. – Sandra Rossi Mar 22 '19 at 13:32
@SandraRossi according to the [online help](https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_itab.htm) the DB only matters if your SELECT actually uses the DB too, like in a JOIN. Why and how would it matter if it is not used? – András Mar 22 '19 at 20:13
@András I don't know why it matters but on my 7.52 ASE system, the code above does a CX_SY_SQL_UNSUPPORTED_FEATURE "Unsupported database extension". So, about "[This statement cannot be executed](https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abennews-752-open_sql.htm#!ABAP_MODIFICATION_1@1@) on all database systems, if the data from the internal table needs to be passed to the database", the question is WHEN does it need to be passed? – Sandra Rossi Mar 22 '19 at 20:57
@SandraRossi I guess when you do a join using database table and internal table – Haojie Mar 23 '19 at 04:47
@RaúlD.Martín oh.i will come up one solution for 7.4 – Haojie Mar 23 '19 at 05:02
@Haojie As I said, it's not only in case of a join, your code fails on my 7.52 ASE system; but it doesn't fail anymore if I remove the GROUP BY/ORDER BY. I feel that the restrictions are somewhat related to the ones of [Table Buffering](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abenbuffer_restrictions.htm), although it also depends on the database system. – Sandra Rossi Mar 23 '19 at 08:19
@RaúlD.Martín please check my new answer for 740. Thank you! – Haojie Mar 23 '19 at 10:45
1

You should test if this select statement is processed by database (you probably don't want this) before committing to this. Previously [SELECT - FROM @itab](https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_itab.htm) (or maybe one of change summary docs in Learning Hub) explicitly said that aggregations, functions and complex selection conditions (anything other than X = Y) in queries are performed on DB. It no longer explicitly states that, but instead states data is only transported if necessary and tells you to verify that yourself. – Zero Jun 06 '22 at 07:21

András · Answer 2 · 2022-09-18T14:22:12.313

3

Gather to Hashed table

This works with any release currently supported, is faster¹ than a SORT + DELETE ADJACENT DUPLICATES, and leaves the original table intact.

FIELD-SYMBOLS: <fs_itab> LIKE LINE OF lt_original.

DATA: ls_itab   LIKE LINE OF lt_original,
      lt_hashed TYPE HASHED TABLE OF itab WITH UNIQUE KEY vkont.

LOOP AT lt_original INTO ls_itab.
  READ TABLE lt_hashed ASSIGNING <fs_itab>
      WITH KEY vkont = ls_itab-vkont.
  IF sy-subrc = 0.
    IF ls_itab-exbel > <fs_itab>-exbel.
      <fs_itab>-exbel  = ls_itab-exbel.
      <fs_itab>-sparte = ls_itab-sparte.  "remove this if not needed"
    ENDIF.
  ELSE.
    INSERT ls_itab INTO TABLE lt_hashed.
  ENDIF.
ENDLOOP.

SORT + DAD has a speed scaling of O(n*log(n)), while this has O(n)

edited Sep 18 '22 at 14:22

answered Mar 22 '19 at 09:09

András

1,326
4
16
26

Remark for 1) Why not using the statement `COLLECT` as it's also using a (internal) hashed table? It would be a shorter code too, and would be probably faster because it's done by the Kernel (no ABAP byte code interpretation). – Sandra Rossi Mar 22 '19 at 13:35
2

COLLECT can't find MAX, only SUM. So we get the wrong values faster – András Mar 22 '19 at 20:07

score 2 · Answer 3 · answered May 29 '19 at 13:02

Here is REDUCE solution, REDUCE operator is iavailable since ABAP 7.40 SP08.

TYPES: BEGIN OF ty_s_value,
        sparte TYPE char2,
        vkont  TYPE char12,
        exbel  TYPE char16,
       END OF ty_s_value.
TYPES: ty_t_value TYPE STANDARD TABLE OF ty_s_value WITH EMPTY KEY.

DATA(lt_tab) = 
VALUE ty_t_value( 
                 ( sparte = '05' vkont = '800000008422' exbel = '1NSN150900000058')
                 ( sparte = 'L2' vkont = '800000008422' exbel = '1NSN150900000058')
                 ( sparte = '05' vkont = '800000008422' exbel = '1NSN150900000037')
                 ( sparte = 'L2' vkont = '800000008422' exbel = '1NSN150900000037')
                 ( sparte = '05' vkont = '800000008422' exbel = '1NSN150900000013')
                 ( sparte = 'L2' vkont = '800000008422' exbel = '1NSN150900000013')
                 ( sparte = '05' vkont = '800000008415' exbel = '1HSN151200000009')
                 ( sparte = 'S1' vkont = '800000008415' exbel = '1HSN151200000009')
                 ( sparte = '05' vkont = '800000008415' exbel = '1HSN151200000008')
                 ( sparte = 'S1' vkont = '800000008415' exbel = '1HSN151200000008')
                 ( sparte = 'L1' vkont = '800000008422' exbel = '1NSN150900000050')
                 ( sparte = 'L1' vkont = '800000008422' exbel = '1NSN150900000029')
                ...
                ).

DATA(lt_result) = 
VALUE ty_t_value( FOR GROUPS <group_key> OF <wa> IN lt_tab 
                  GROUP BY ( sparte = <wa>-sparte vkont = <wa>-vkont )
                  LET max2 = 
                  REDUCE #( INIT max = 
                            VALUE ty_s_value( )
                            FOR <m> IN GROUP <group_key>
                            NEXT max = COND #( WHEN <m>-exbel > max-exbel THEN <m> ELSE max ) )
                  IN ( max2 ) ).

BTW, your expected resultset is missing L1 line, I assume in your selection you respect not only VKONT but SPARTE too.

score 1 · Answer 4 · answered Mar 21 '19 at 16:19

1

You can use a SORT followed by a DELETE ADJACENT DUPLICATES since the latter will delete all rows in certain groups of rows, except for the first row of the group.

SORT itab BY vkont exbel DESCENDING. " Group by VKONT and put highest EXBEL in the group first
DELETE ADJACENT DUPLICATES FROM itab COMPARING VKONT.

There might be more efficient ways if you need to keep the original itab intact.

answered Mar 21 '19 at 16:19

Gert Beukema

2,510
1
17
18

Thanks for your answer, but with this code I just get 1 register per vkont, instead of all registers of that vkont that have the same highest exbel. – Raúl D.Martín Mar 22 '19 at 07:16

Select max field value from a group of records?

4 Answers4

Gather to Hashed table