Question about Select Statements

[lcs3uapb]

hello everybody i was wondering if i could get some help or could somebody point me in the right direction. i am very new to abap programming. i think i mite have start using abap about an month ago. here is an example of what im trying to do below.


im starting with this

Code: Select all

rowNum     col1    col2    col3       col4
1             1234     bp     name1    1234
2             1234     py     name1    1234
3             1223     bp     name2    1223
4             1245     py     name3    1245
5             5432     py     name4    4432

i want to delete row# 1 and 2 because they have the same col1# but
different col2#

Code: Select all

[b]this is the result i want:

rowNum     col1    col2      col3       col4
1              1223     bp     name2    1223
2              1245     py     name3    1245
3              5432     py     name4    5432

but this is the result im getting:

Code: Select all

rowNum     col1    col2     col3        col4
1              1234     bp     name1    1234
2              1223     bp     name2    1223
3              1245     py     name3    1245
4              5432     py     name4    4432

i have tried this code.

Code: Select all

SELECT knvp~kunnr kna1~name1 knvp~parvw knvp~kunn2
    FROM kna1 JOIN knvp ON kna1~kunnr = knvp~kunnr
    INTO TABLE itab
    WHERE knvp~kunnr IN ( SELECT kna1~kunnr
                          FROM kna1 JOIN knvp ON kna1~kunnr = knvp~kunn2
                          WHERE ( (  knvp~kunnr = kna1~kunnr  AND kna1~ktokd = 'ZPYR'
                                 AND knvp~parvw = 'RG' ) OR ( kna1~ktokd = 'ZBPR'
                                 AND knvp~parvw = 'RE' )       )
                          GROUP BY kna1~kunnr
                          HAVING COUNT( DISTINCT knvp~kunn2 ) < 2 ).
  DELETE ADJACENT DUPLICATES FROM itab COMPARING kunnr kunn2.

[YuriT]

DELETE ADJACENT DUPLICATES does not delete both identical lines, only extra ones, leaving the original (row 1 in your case). This is your problem