ABAP Developmentintermediate

Internal Tables Part 7: Removing Duplicates and Building Unique Datasets

Key takeaway

Remove duplicate rows reliably: SORT + DELETE ADJACENT DUPLICATES with COMPARING, unique-key tables, collecting unique keys for FOR ALL ENTRIES, and the modern GROUP BY alternative.

Explanation

📌 KEY POINTS
✅ DELETE ADJACENT DUPLICATES only removes duplicates that are next to each other — SORT first by the same fields.
✅ COMPARING defines which fields make a row a duplicate (default: the table key or all fields).
✅ Unique sorted/hashed tables prevent duplicates at insert time.
💡 Always de-duplicate the driver table before FOR ALL ENTRIES — fewer database round trips.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Part 6 changed tables. A very common task is getting a unique list — unique airlines, unique customers — for output or for the next SELECT.

━━━━━━━━━━━━━━━━━━━━
▶ 1. THE CLASSIC PATTERN
SORT gt_book BY carrid connid.
DELETE ADJACENT DUPLICATES FROM gt_book COMPARING carrid connid.

Rows are compared with their predecessor; only adjacent equal rows are removed.
Without SORT (or with a different sort order), duplicates remain.
COMPARING ALL FIELDS removes only fully identical rows.

━━━━━━━━━━━━━━━━━━━━
▶ 2. BUILDING A UNIQUE KEY LIST (FOR ALL ENTRIES DRIVER)
To read customers for 10,000 bookings, extract the customer numbers first, make them unique, then select:

Copy only the key field into a small table (LOOP + APPEND or VALUE with FOR).
SORT + DELETE ADJACENT DUPLICATES.
Check the table is not empty, then SELECT … FOR ALL ENTRIES IN (Open SQL topic).

━━━━━━━━━━━━━━━━━━━━
▶ 3. UNIQUE TABLES

DATA gt_cust TYPE SORTED TABLE OF ty_cust WITH UNIQUE KEY customid.
INSERT … INTO TABLE — duplicates are rejected (sy-subrc = 4), no error.
Good when you build the list incrementally.

⚠️ WATCH OUT: APPEND into a unique sorted table with a duplicate key causes a runtime error; use INSERT INTO TABLE.

━━━━━━━━━━━━━━━━━━━━
▶ 4. SIDE NOTE: GROUP BY (7.40+)
LOOP AT gt_book INTO DATA(ls) GROUP BY ls-customid INTO DATA(lv_cust).
APPEND lv_cust TO lt_unique.

ENDLOOP.

Or VALUE #( FOR GROUPS g OF b IN gt_book GROUP BY b-customid ( g ) ). — readable when you also need per-group processing (Part 8).

━━━━━━━━━━━━━━━━━━━━
▶ 5. IN THE DATABASE INSTEAD?
If the unique list comes straight from the database, SELECT DISTINCT is simplest — no need to read duplicates into memory.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: create a unique customer list from bookings three ways and use it as a FOR ALL ENTRIES driver.

✅ CHECKPOINT: you produce unique datasets reliably and know why SORT comes first.

Code example

ABAP Code

REPORT zitab_part7. TYPES: BEGIN OF ty_cust,         customid TYPE s_customer,       END OF ty_cust. DATA: gt_book   TYPE STANDARD TABLE OF sbook,      gs_book   TYPE sbook,      gt_cust   TYPE STANDARD TABLE OF ty_cust,      gs_cust   TYPE ty_cust,      gt_cust_u TYPE SORTED TABLE OF ty_cust WITH UNIQUE KEY customid,      gt_scust  TYPE STANDARD TABLE OF scustom,      gv_lines  TYPE i. START-OF-SELECTION.  SELECT * FROM sbook INTO TABLE gt_book UP TO 2000 ROWS WHERE carrid = 'LH'. * ===== 1. Classic: key list + SORT + DELETE ADJACENT DUPLICATES =====  LOOP AT gt_book INTO gs_book.    gs_cust-customid = gs_book-customid.    APPEND gs_cust TO gt_cust.  ENDLOOP.  SORT gt_cust BY customid.                                  " SORT FIRST!  DELETE ADJACENT DUPLICATES FROM gt_cust COMPARING customid.  DESCRIBE TABLE gt_cust LINES gv_lines.  WRITE: / 'unique customers (classic):', gv_lines. * ===== 2. Unique sorted table: duplicates rejected on insert =====  LOOP AT gt_book INTO gs_book.    gs_cust-customid = gs_book-customid.    INSERT gs_cust INTO TABLE gt_cust_u.                     " sy-subrc 4 for duplicates  ENDLOOP.  DESCRIBE TABLE gt_cust_u LINES gv_lines.  WRITE: / 'unique customers (unique key):', gv_lines. * ===== 3. Use as FOR ALL ENTRIES driver (check not empty!) =====  IF gt_cust IS NOT INITIAL.    SELECT id name city FROM scustom      INTO CORRESPONDING FIELDS OF TABLE gt_scust      FOR ALL ENTRIES IN gt_cust      WHERE id = gt_cust-customid.  ENDIF.  DESCRIBE TABLE gt_scust LINES gv_lines.  WRITE: / 'customers read:', gv_lines. * ===== 4. Straight from the database =====*  SELECT DISTINCT customid FROM sbook INTO TABLE gt_cust WHERE carrid = 'LH'. * 💡 Modern alternative (7.40+):*    LOOP AT gt_book INTO DATA(ls) GROUP BY ls-customid INTO DATA(lv_cust).*      APPEND lv_cust TO lt_unique.*    ENDLOOP.

Real project scenario

A report used DELETE ADJACENT DUPLICATES on a customer list that had been sorted by city, not by customer number. The output still contained duplicates, and the follow-up FOR ALL ENTRIES read the same customers many times — the SQL trace showed 12,000 selects for 3,000 customers.
Sorting by the compared field before deleting duplicates fixed both the output and the performance; the team added a comment rule "SORT directly above DELETE ADJACENT DUPLICATES, same fields".

Common mistakes

DELETE ADJACENT DUPLICATES without sorting first.
Sorting by different fields than COMPARING.
APPEND of duplicates into unique sorted tables (runtime error).
Passing duplicate-filled tables to FOR ALL ENTRIES.
Reading duplicates from the database instead of SELECT DISTINCT.

Best practices

SORT by exactly the COMPARING fields directly before DELETE ADJACENT DUPLICATES.
Use unique sorted/hashed tables when building lists incrementally.
De-duplicate and check non-empty before FOR ALL ENTRIES.
Use SELECT DISTINCT when possible.
Use GROUP BY when you also process per group.

Interview angle

Questions: "How do you remove duplicates from an internal table?", "Why must you sort first?", "How do you prepare a FOR ALL ENTRIES driver table?"
Strong answer: SORT by the relevant fields and DELETE ADJACENT DUPLICATES COMPARING the same fields, because only adjacent duplicates are removed; alternatively use unique-key tables with INSERT INTO TABLE or LOOP GROUP BY; for FOR ALL ENTRIES extract the key fields, de-duplicate and check the table is not empty.

Practice this lesson

Quick check

1. DELETE ADJACENT DUPLICATES COMPARING customid leaves duplicates in the table. What is the most likely cause?

2. For DELETE ADJACENT DUPLICATES FROM itab COMPARING key_fields to reliably eliminate every duplicate row based on those fields, what precondition must be satisfied?

3. In a SELECT statement using the addition FOR ALL ENTRIES IN itab, what is the result if itab is completely empty at runtime?

Interview questions

1. A bulk material lookup built a FOR ALL ENTRIES driver table without removing duplicates or checking emptiness. What problems arise and how do you fix them?

Answer it in your own words first, then compare.

2. How would you delete duplicates from a large standard internal table efficiently while keeping the first occurrence of each key?