Select row from table where column value has "highest priority"

Question

I've got a database table with the columns DOC1, DOC2 and CLIENT. I'm trying to select one row per CLIENT, whose DOC1 column has the highest priority based on the following order, from highest to lowest priority: ITCI > ITPP > ITPS > ITPT.

Here is an example.

INPUT

DOC1  DOC2  CLIENT
ITCI  GG319  101
ITPS  YB311  102  
ITPT  GG319  101
ITPP  YB311  102

OUTPUT

The destination table should have CLIENT has unique key and I have to add the two columns DOC1 and DOC2, by taking the line which has DOC1 with the highest priority.

CLIENT DOC2 DOC1
101   GG319 ITCI
102   YB311 ITPP

I wrote a select single in end routine but there was a syntax error:

Select single doc1  doc2 (W_doc1, W_doc2)
        FROM /BI0/Pdoctax
        WHERE  client eq <RESULT_FIELDS>-client. 

Answer 1

As your priority order from highest to lowest fully resemble alphabetical order you need nothing more than use GROUP BY with MIN aggregation:

SELECT client, MIN( doc2 ) AS doc2, MIN( doc1 ) AS doc1
INTO TABLE @DATA(itab)
FROM /BI0/Pdoctax
GROUP BY client.

In more complex cases where collation is not as primitive you can utilize CASE clause.

Answer 2

If I understand this right, you want the select to output only one line per CLIENT, choosing the line from the original table based on those DOC1 priorities.

A selection like that is not possible.

On the top of my head, these are the possibilities I come up with, but there's probably more. Depending on your situation, you will have to figure out which one fits your needs best.

1. You could select everything, LOOP over it and create your format with IF conditions etc.

2. If you really only have those 4 DOC1 possibilities, you could select everything, sort the internal table by CLIENT and DOC1 and then delete adjacant duplicates comparing CLIENT only. This would work because "ITCI >ITPP >ITPS>ITPT" is in alphabetical order.

3. Again if you only have those 4 DOC1 possibilities, then you could select them after each other, checking in between if there is still CLIENTS that are missing.

Answer 3

Since your doc1 priority values are actually six, this should work.

SELECT b~client,
       coalesce( p1~doc1, p2~doc1, p3~doc1, p4~doc1, p5~doc1, p6~doc1 ),
       coalesce( p1~doc2, p2~doc2, p3~doc2, p4~doc2, p5~doc2, p6~doc2 )
  FROM /bi0/pdoctax AS b
                 LEFT OUTER JOIN /bi0/pdoctax AS p1
                 ON  p1~client = b~client
                 AND p1~doc1   = 'ITCI'
                 LEFT OUTER JOIN /bi0/pdoctax AS p2
                 ON  p2~client = b~client
                 AND p2~doc1   = 'ITPS'
                 LEFT OUTER JOIN /bi0/pdoctax AS p3
                 ON  p3~client = b~client
                 AND p3~doc1   = 'ITPT'
                 LEFT OUTER JOIN /bi0/pdoctax AS p4
                 ON  p4~client = b~client
                 AND p4~doc1   = 'ITPT'
                 LEFT OUTER JOIN /bi0/pdoctax AS p5
                 ON  p4~client = b~client
                 AND p4~doc1   = 'P5'
                 LEFT OUTER JOIN /bi0/pdoctax AS p6
                 ON  p4~client = b~client
                 AND p4~doc1   = 'P6'
  WHERE b~client = @<result_fields>-client
   INTO @DATA(ls_doctax).

I am assuming you are looking for an answer that does this in a single SELECT statement, otherwise you can loop:

  DATA lt_priorities TYPE STANDARD TABLE OF /bi0/pdoctax-doc1.
  lt_priorities = VALUE #( ( 'ITCI' ) ( 'ITPS' ) ( 'ITPT' ) ( 'ITPP' ) ( 'P500' ) ( 'P600' ) ).

  SELECT b~client,
         doc1,
         doc2
    FROM /bi0/pdoctax AS b
   WHERE b~client = @<result_fields>-client
   ORDER BY doc1 ASCENDING
    INTO TABLE @DATA(lt_doctax).

  DATA ls_doctax_filtered LIKE LINE OF lt_doctax.

  LOOP AT lt_priorities ASSIGNING FIELD-SYMBOL(<fs_priority>).
    READ TABLE lt_doctax ASSIGNING FIELD-SYMBOL(<fs_doctax>)
      WITH KEY doc1 = <fs_priority> BINARY SEARCH.
    IF sy-subrc = 0.
      ls_doctax_filtered = <fs_doctax>.
*     --->
      EXIT.
    ENDIF.
  ENDLOOP.

Answer 4

Here is a "simple" solution with ABAP 7.52.

I used another example for the tests so that anyone can play with it: I used the demo table SFLIGHT that is provided with any ABAP installation. Run the program SAPBC_DATA_GENERATOR to generate data if the table is empty.

As already mentioned by other people, it's assumed that your priority is based on the alphabetic order, so the aggregate function MIN can be used.

The code below gets the lines of SFLIGHT that matches each distinct value of the column CARRID (equivalent to CLIENT in your question) and its minimal value in the PAYMENTSUM (DOC1) column (both inside the EXISTS subquery):

SELECT carrid, fldate AS doc2, paymentsum AS doc1
FROM sflight AS a
WHERE EXISTS (
        SELECT carrid
        FROM sflight
        WHERE carrid = a~carrid
        GROUP BY carrid
        HAVING MIN( paymentsum ) = a~paymentsum )
INTO TABLE @DATA(itab).

Contents of database table SFLIGHT (via SE16/expected result highlighted):

Contents of internal table itab (via debug/as expected):

Note about the ABAP SQL code:

• Any selection on an aggregation result can be only done after HAVING, not after WHERE (classic SQL rule).
• If several lines of SFLIGHT have the same values for CARRID and PAYMENTSUM, then one of those lines is randomly selected (classic SQL rule).
• It works in ABAP 7.52 but may not work in older versions.
• For future visitors having a more complex request, that solution may quickly become impossible to adapt.
• There might be some easier solutions in "native SQL" (direct use of the database SQL), like using SELECT ... FROM ( SELECT ... ) (not allowed up to ABAP SQL 7.53)