Select min set of rows that match query

[yasser_aoujil]

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.

[horst_keller]

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.

[raghug]

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.

[yasser_aoujil]

Thank you for explaining that, I didn't know it, I am new to SAP.

[yasser_aoujil]

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.

[Jelena]

I'm honestly lost here... SELECT returned what you asked for. Considering data in the table, based on what criteria do you expect to get the "desired" result?

SELECT DISTINCT can be used to get only unique values in a specific column but not in a combination of columns as you seem to expect for some reason.

You might want to clarify what exactly is the logic.

[yasser_aoujil]

Sorry for responding late, I couldn't do it sooner.

I will explain, as I am new to ABAP I was thinking that it allows for complex aggregation and group by queries.

Original problem is, having this table ZTEST:

I have a table ZTEST with these data:

COMB IDENS

1 20003

2 20011

3 20041

4 20003

4 20011

5 20011

5 20041

6 20041

6 20003

7 20003

7 20011

7 20041

As you can see, the PK is the set (COMB, IDENS). Now, during program execution, we only have IDENS values in an internal table, it could have any of the combination sets or subsets of these values 20003, 20011 and 20041 (Possible values of ZTEST-IDENS). So, the goal is to get the value of ZTEST-COMB that matchs exactly the content of the internal table, for example if the internal table it_idens contains [20011,20041] then I need to get the value 5. The best thing I did in code is something like:

select comb from ztest
    into corresponding fields of table tl_combs 
    where idens in it_idens. 

After this select with content of it_idens of above is [2, 3, 4, 5, 5, 6, 7, 7] and I needed only 5 or [5, 5] because if you at ZTEST the set that match the exact combination of idens in the internal table is the one that has comb=5.

I hope I explained.

Thank you.

[Jelena]

What Raymond said. This is a very weird requirement, to be honest, but essentially you need two SELECTs: one to get the respective COMB values and second to verify that there is no entry for the same COMB but different IDENS. That's the gist of it.

[raghug]

I have not tried this... it is a shot in the dark 🙂 The selected_combs should have your value at the end. range_combs is declared as TYPE RANGE OF ztest. If you are on ABAP < 7.4, you will have to declare a work area for the range, fill it and append that to the range.

LOOP AT it_idens INTO idens.
  SELECT DISTINCT combs  
    FROM ztest
    WHERE combs IN range_combs
    AND idens = idens
    ORDER BY comb
    INTO TABLE @DATA(selected_combs).
    
  IF sy-subrc <> 0.
    REFRESH range_combs.
    LOOP AT selected_combs INTO l_comb.
      range_combs = VALUE #( sign = 'I' option = 'EQ' low = l_comb ).
    ENDLOOP.
  ENDIF.
ENDLOOP.

[yasser_aoujil]

That's what I did, thank you.

[yasser_aoujil]

I didn't test it, but I did something close to that, thank you.

[raymond_giuseppi]

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