How to set duplicate values in a column to zero while not deleting a row in SQL/Impala?

Question

I am trying to find a way how to set all but one duplicate values in a column to zero without deleting the row. Below is a simplified example that displays the general idea. The column where the duplicate value needs to be set to zero is 'Total Amount' in case there is more than one entry for the same order.

Initial Data Structure

N   Date    Order   Total Amount
1   31.12   10007   100,00
2   31.12   10007   100,00

Desired Data Structure

N   Date    Order   Total Amount
1   31.12   10007   100,00
2   31.12   10007     0,00

I hope this is possible. I would highly appreciate your help!

Maxi

Answer 1

You can do it with EXISTS:

select 
  t.productcode, t.orderdate, t.order,
  case when exists (
      select 1 from tablename
      where productcode < t.productcode and 
      orderdate = t.orderdate and order = t.order and
      amount = t.amount
    ) then 0.0 else t.totalamount end totalamount
from tablename t

Answer 2

You can do this in a select use case and row_number():

select N, Date, OrderNum,
       (case when row_number() over (partition by ordernum order by n) = 1
             then Total_Amount
             else 0
        end) as total_amount
from t;

Note: order is a really bad name for a column because it is a SQL keyword. That is why I changed it to ordernum.