CODEWITHSUNDEEP
teradata
puneet madan
puneet madan

Reputation: 3

how to apply ranking in teradata

I am currently working with multiple tables. table1 which is to be lookup with database. for ex. I have worked on excel sheet and updated records in the DB and now need to lookup with DB whether records have been updated correctly or not and when was the record updated. so I have table 1 which contains my data been worked upon and to be lookedup with DB. basically, am unioning all records from the DB and applying ranking then lookup with my table1 been worked upon. record could have been changed many times, I need the latest date so I applied ranking. can you please help in correcting this. as am not able to get desired results

select a.*, b.update_dt||' '||c.udpate_dt|| as update_date
from table1 a
left join
 ( select distinct customer_id, max(last_updated_dt) as update_dt
   qualify rank() over(partition by customer_id order by Last_updated_dt) as rank
   from table2
   group by customer_id
 ) b
on a.customer_id=b.customer_id

left join
 ( select distinct customer_id, max(last_updated_dt) as update_dt
   qualify rank() over(partition by customer_id order by last_updated_dt) as rank
   from table3
   group by customer_id
 )c
on a.customer_id=c.customer_id

Upvotes: 0

Views: 257

Answers (1)

dnoeth
dnoeth

Reputation: 60482

You need either max(last_updated_dt) or QUALIFY, but not both:

select a.*, b.update_dt||' '||c.udpate_dt|| as update_date
from table1 a
left join
 ( select customer_id, max(last_updated_dt) as update_dt
   from table2
   group by customer_id
 ) b
on a.customer_id=b.customer_id

left join
 ( select customer_id, max(last_updated_dt) as update_dt
   from table3
   group by customer_id
 )c
on a.customer_id=c.customer_id

If you need other columns besides the max date:

select a.*, b.update_dt||' '||c.udpate_dt|| as update_date
from table1 a
left join
 ( select customer_id, last_updated_dt as update_dt, ...
   from table2
   qualify row_number() over(partition by customer_id 
                       order by last_updated_dt DESC) = 1
 ) b
on a.customer_id=b.customer_id

left join
 ( select customer_id, last_updated_dt as update_dt, ...
   from table3
   qualify row_number() over(partition by customer_id 
                       order by last_updated_dt DESC) = 1
 )c
on a.customer_id=c.customer_id

Upvotes: 3

Related Questions