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    

[lbreddemann]

DELETE on table variables is only available starting with HANA 2. Do you use a HANA 2?

Besides that, using the internal column store column $rowid$ has never been supported - using it to eliminate duplicate records in the absence of a key is not the brightest idea.

What you can easily do in your case is to simply SELECT DISTINCT from your table variable.

tab_dupes = SELECT ... FROM all_my_dupes;
tab_nodupes = SELECT DISTINCT * FROM :tab_dupes;

That's it. No weird internal column stuff, no difficult update on table variables.

For scenarios where you have duplicates only on the subset of columns, e.g. your key-columns to-be, you can use the standard options like picking the largest value (if "largest" is unique) or just get the first/last entry. But that's all standard SQL, too...

[Former Member]

Thanks, Lars.

I am using Hana 1.0 SPS12.

I am trying to using DISTINCT but more complex task.

I have i db table with stocks history.For example

matnr werks gjahr monat stock

1000000000 1000 2017 05 0

1000000000 1000 2017 04 2

1000000000 1000 2017 01 1

1000000001 1000 2018 02 2

1000000001 1000 2018 03 3

1000000001 1000 2018 01 1

And i need to get the last stock for each group of (matnr+werks)

For using distinct i need to select only matnr+werks, but how i can get last stock number ?

[Former Member]

Thanks, Lars.

It is resolve my problem.