CODEWITHSUNDEEP
sqlteradata
Katie
Katie

Reputation: 362

Teradata SQL Get Rid of Duplicates with Specific Order

I just started teradata SQL this week, so sorry if I don't phrase things correctly. I originally created a script in R that gets rid of duplicates within my table, but now I need to transfer this code into SQL. Here is some sample data:

enter image description here

I want to get rid of any D's in the DELETE column, partition by ID, order by STATUS, DATE, and AMOUNT (with actual dates and amounts before ?s). I want STATUS to go in this order: P, H, F, U, T. I want the first row that has STATUS, DATE, and AMOUNT filled out (with STATUS in order). Here is the example output data:

enter image description here

I'm really stuck on the order issue and the code I've written isn't producing any data at all (but no errors).

SAMPLE CODE:

CREATE VOLATILE TABLE new_tble
AS
(SELECT * 
FROM table
QUALIFY row_number() OVER (partition BY ID ORDER BY ID, DATE, AMOUNT)=1
WHERE DELETE <> 'D'
)
with data;

Upvotes: 0

Views: 991

Answers (1)

dnoeth
dnoeth

Reputation: 60472

This is a direct translation of your description into Teradata SQL, assuming ? means NULL:

select *
from tab
where "delete" is null
  and "date" is not null
  and amount is not null
qualify
   row_number() 
   over (partition by id
         order by case status 
                    when 'P' then 1 
                    when 'H' then 2 
                    when 'F' then 3
                    when 'U' then 4 
                    when 'T' then 5 
                  end
                  ,"date"
                  ,amount) = 1

Upvotes: 2

Related Questions