SqlScript delete duplicates from local table variable

[Former Member]

Hi, experts.

I have a issue. I need to delete duplicates from local table variable in AMDP.

I find this

delete from tab
where "$rowid$" in
(
SELECT LEAD($rowid$) over (partition by field1, field2) from tab
) ;

But for local table variable it is do not work .

[eralper_yilmaz]

To identify and delete duplicates using SQLScript, another method can be using Row_Number() with Partition By clause. The columns that are accepted to define duplicate case can be used in Partition By clause of the Row_Number() function.

Here is a sample

et_data = select kunnr, vbeln from (
    select row_number ( ) over ( partition by kunnr order by vbeln) as rn, * from :lt_data
    ) where rn = 1;

Above SQL returns first order of a customer

If you add more columns in Partition By clause (here is only kunnr used), or all columns than you will have an exact duplicate of the row data.

[Former Member]

Thanks, ERALPER YILMAZ

It is a best way to delete duplicates.

[lbreddemann · Accepted solution]

Now this is a prime example for why one should try to ask about how to achieve something instead of how to do this little step in the overall solution.

Your problem actually is: get me the most current months record for every combination of material, factory and year.

There's a fairly straight forward approach for that.

1. find out the most current month for every material|factory|year.
2. select only those records from the table that are about the most current month.

Step 1 is an easy MAX() aggregation.

Step 2 is an easy filtering via INNER JOIN

select s.matnr, s.werks, s.gjahr, s.monat, s.stock
from stock s
inner join (select matnr, werks, gjahr, max(monat) as max_month
            from stock
            group by matnr, werks, gjahr) ms
       on ( s.matnr, s.werks, s.gjahr, s.monat) 
          = (ms.matnr, ms.werks, ms.gjahr, ms.max_month);

With your demo data this yields:

MATNR       WERKS   GJAHR   MONAT   STOCK
1000000000  1000    2017    05      0    
1000000001  1000    2018    03      3