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]

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.