Selecting the lowest value of a character string in a grouped data

Question

I'm trying to create a SAP Query (SQ02).

My dataset is grouped based on a value of a field (example below). I need to select and output the lowest value of another field, but its a mixed character - Letter + a number, I need the lowest number.

I have this piece of code right now, but it only brings back the value attached to the master account and the logic fails in the master account does not have the lowest value.

Select MIN( KLABC )
  FROM KNVV
  INTO CLS2
  WHERE KUNNR IN
    ( SELECT KUNNR
      FROM KNB1
      WHERE EKVBD = KNB1-EKVBD ).

IF cls2+1(1) > KNVV-KLABC+1(1) AND KNVV-KLABC+0(1) = 'R'.
  clear CLS2.
  Select MIN( KLABC )
    FROM KNVV
    INTO CLS2
    WHERE KLABC LIKE 'R%'
      AND KUNNR IN
        ( SELECT KUNNR
          FROM KNB1
          WHERE EKVBD = KNB1-EKVBD ).
ENDIF.

Example dataset:

Customer  Class  Group
1001      R1     1001
301048    R2     1001
10015     A1     10015
306069    A2     10015
6042482   R1     10025
10027     R1     10027
303226    R2     10027
10028     A4     10028
306070    A4     10028
10034     A2     10034
303724    A5     10034
403558    A7     10034
5042      A1     10047
302673    A3     10047

The intermediate result would correspond to something like SELECT MIN( class ) AS Class2, group FROM abovetable GROUP BY group would give:

Class2 Group
R1     1001
A1     10015
R1     10025
R1     10027
A4     10028
A2     10034
A1     10047

By merging the intermediate result, the final result would look something like this:

Customer  Class  Group  Class2
1001      R1     1001   R1
301048    R2     1001   R1
10015     A1     10015  A1
306069    A2     10015  A1
6042482   R1     10025  R1
10027     R1     10027  R1
303226    R2     10027  R1
10028     A4     10028  A4
306070    A4     10028  A4
10034     A2     10034  A2
303724    A5     10034  A2
403558    A7     10034  A2
5042      A1     10047  A1
302673    A3     10047  A1

Just the number would suffice too.

Yep, try to describe task more clearly, nothing can be understood from these datasets. What is the grouping field? Class? What field you want to fetch? Class again? The minimum in each class group? How does `class2` field is constructed? Your select just fetches classes for **single** customer which contradicts to your given datasets. — Suncatcher, Aug 09 '19 at 18:10
Class2 is what the final result should look like. Customers are group by the Group field, up to 3 per number (3 companies) and each individual customer has a class assigned, but they are not in line between the companies (within the same group). — DDoze, Aug 10 '19 at 17:37
Iam looking for a piece of code that looks at the class field, then at the group field and fetches the lowest numbered value(character?) within that group. (A1 - lowest, A7 - highest etc). The character can be disregarded (A, R) — DDoze, Aug 10 '19 at 17:44
I edited the question: I propose to add an intermediate result to clarify the question. You should better show what you tried, the query shown is not even an attempt. — Sandra Rossi, Aug 10 '19 at 23:22
Sorry, I should have mentioned I am doing this in SQ02 where I have a table join via KNA1 > KNB1 > KNVV on KUNNR. So that simple select brings back the desired result IF the master account (also group number) has the lowest Class. — DDoze, Aug 12 '19 at 07:12
I've been able to get a little closer to the desired results. With the following code: Select MIN( KLABC ) FROM KNVV INTO CLS2 WHERE KUNNR IN ( SELECT KUNNR FROM KNB1 WHERE EKVBD = KNB1-EKVBD ). IF cls2+1(1) > KNVV-KLABC+1(1) AND KNVV-KLABC+0(1) = 'R'. clear CLS2. Select MIN( KLABC ) FROM KNVV INTO CLS2 WHERE KLABC LIKE 'R%' AND KUNNR IN ( SELECT KUNNR FROM KNB1 WHERE EKVBD = KNB1-EKVBD ). ENDIF. Now the only issue is if the lowest value starts with R, it wont populate all the relevant fields. Need help with the 2nd select. — DDoze, Aug 12 '19 at 09:39
Please post your last edit as an answer to your own question. — Jagger, Aug 13 '19 at 10:56

score 0 · Accepted Answer · edited Aug 14 '19 at 13:47

The following code will bring back the lowest valued entry in the group.

Select MIN( KLABC )
  FROM KNVV
  INTO CLS2
  WHERE KLABC LIKE 'R%'
    AND KUNNR IN
      ( SELECT KUNNR
        FROM KNB1
        WHERE EKVBD = KNB1-EKVBD 
          AND KUNNR IN 
            ( SELECT KUNNR
              FROM KNA1
              WHERE LOEVM NE 'X' ) ).
IF CLS2 = ''.
  Select MIN( KLABC )
    FROM KNVV
    INTO CLS2
    WHERE KLABC LIKE 'A%'
      AND KUNNR IN
        ( SELECT KUNNR
          FROM KNB1
          WHERE EKVBD = KNB1-EKVBD 
            AND KUNNR IN 
              ( SELECT KUNNR
                FROM KNA1
                WHERE LOEVM NE 'X' ) ).
  IF CLS2 = ''.
    Select MIN( KLABC )
      FROM KNVV
      INTO CLS2
      WHERE KUNNR IN
        ( SELECT KUNNR
          FROM KNB1
          WHERE EKVBD = KNB1-EKVBD 
            AND KUNNR IN 
              ( SELECT KUNNR
                FROM KNA1
                WHERE LOEVM NE 'X' ) ).
  ENDIF.
ENDIF.

As another requirement I also had to make the sure Customer class 1st letter matches with the result field, I've created an extra field with this code. It will match the original Class Letter and the value picked for the group by the above code.

CLEAR CLS1.
IF CLS2 NE ' '
      AND KNVV-KLABC+0(1) = 'R' OR KNVV-KLABC+0(1) = 'A'.
  CONCATENATE KNVV-KLABC+0(1) CLS2+1(1)
    INTO CLS1.
ELSE.
  CLS1 = CLS2.
ENDIF.

Selecting the lowest value of a character string in a grouped data

1 Answers1