Skip to Content
avatar image
-4
Former Member

Select min set of rows that match query

Hi to everyone,

I have a hard a time finding a solution for this requirement.

I have a table ZTEST with these data:

COMB IDENS

1 20003

2 20003

2 20011

3 20003

3 20011

3 20041

4 20011

4 20041

I need to select from ZTEST the data that match the a combination of the field IDENS.

For example, for these values [20003,20011] in a program (the query only uses IDENS field), I need to get back from the query the following set:

2 20003

2 20011

All the tests I did always gave mixed COMB fields, for example for the select fields above I get:

1 20003

2 20003

2 20011

3 20003

3 20011

Any help would be much apreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jan 31, 2017 at 03:06 PM

    If you are dealing with internal tables, Horst Keller is correct. If you are dealing with a database and trying to do a select statement, then first read about aggregates and use SELECT MIN SELECT - aggregate - ABAP Keyword Documentation.

    SELECT MIN( comb ) FROM ztest
      WHERE idens = '20003'.

    If you want both of them going into a structure, you could do something like this instead.

    SELECT comb, idens FROM ztest
      WHERE idens = '20003'
      ORDER BY comb ASCENDING
      INTO CORRESPONDING FIELDS OF @zitab
      UP TO 1 ROWS

    Depending on your keys and indexes on the ZTEST table - and if you have more fields than the IDENS to deal with, you could use the results of the first example in a sub-query. So you will get something like this...

    SELECT SINGLE * FROM ztest
      WHERE comb = ( SELECT MIN( comb ) FROM ztest WHERE idens = '20003' )
      AND idens = '20003'
      INTO @zitab.

    Note: The order of the commands and use of the escaped (@) host variables is in the new OpenSQL syntax. You can easily change this to an older syntax if you need to.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31, 2017 at 12:53 PM

    Something like

    SORT ztab BY comb idens. 
    DATA result LIKE ztab.
    result = VALUE #(  ( ztest[ idens = 20003] ) ( ztest[ idens = 20011 ] ) ).

    should do ...

    If you can't use expression syntax, use two READ and INSERT statements.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 22, 2017 at 02:15 PM

    If I understand your requirement, not sure a single SQL command would be able to SELECT the couples COMB, IDENS where there are exactly one occurrence for each COMB of a list of IDENS.

    Some pseudo SQL would be SELECT comb, idens FROM ztest AS a WHERE a~IDENS IN @range_of_idens AND @lines(range_of_idens) EQ ( select count(*) FROM ztest AS b WHERE b~comb EQ a~comb AND b~IDENS IN @range_of_idens ).

    Regards,
    Raymond

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 07, 2017 at 08:51 AM

    Thank you, both of you, I used a mixture of your answers, it helped to solve the problem but I had to add one more field and use abap code to get the desired result, I think ABAP doesn't have complex aggregation selects.

    The same table of above now looks like this, with a field as a counter of repeti:

    COMB IDENS COUNT

    1 20003 1

    2 20003 2

    2 20011 2

    3 20003 3

    3 20011 3

    3 20041 3

    4 20011 2

    4 20041 2

    And I have the following example:

    ranges: rl_ens for ztest-idens.
    data wl_numens type i.
    begin of ty_comb,
       idcomb type ztest-comb,
       idens type ztest-idens,
    end of ty_combinacion.
    
    data: tl_combs type table of ty_comb.
    
    rl_ens-sign = 'I'.
    rl_ens-option = 'EQ'.
    rl_ens-low = '20003'.
    append rl_ens.
    
    rl_ens-sign = 'I'.
    rl_ens-option = 'EQ'.
    rl_ens-low = '20011'.
    append rl_ens.
    
    select comb, idens
        from ztest
        into corresponding fields of table tl_combs
        where count = 2
        and idens in rl_ens.    "rl_ens is a range with idens to query
    

    The select above returns:

    2 20003

    2 20011

    4 20011

    but the desired result is just the two first rows:

    2 20003

    2 20011

    As they are the ones that match exactly the range of idens, I managed to get it with abap code using a loop looking for the highest count, but my original doubt was if it was possible to have this result straight from the select.

    Thank you.

    Add comment
    10|10000 characters needed characters exceeded