Teradata - find a value from preceding second row

user3150378 0
Viewed 28
WITH data (cust, amt, rnk) AS (
VALUES(a, 10, 1),
    (a, 5, 2),
    (a, 15, 3),
    (b, 20, 1),
    (b, 30, 2),
    (c, 3, 1))

I have the data like above. I need to find the amt for each cust where either if the max(rnk) = 1 then amt from that row. Else amt from rnk = 2. So the result would be as below:

a,5,2
b,30,2
c,3,1

I can't seem to get to that. Any help would be great. Thanks.

teradata sql
2 Answers

I believe Teradata supports CTE:

with max_rank as (
  select cust, max(rnk) as m_rank
  from data
  where rnk in (1,2)
  group by cust
  )
select d.cust, d.amt, d.rnk
from data d
join max_rank r
  on d.cust = r.cust
 and d.rnk = r.m_rank
cust amt rnk
a 5 2
b 30 2
c 3 1

fiddle

Isolated0

You can filter the matching row using rank/row_number

select *
from data
where rnk <= 2
qualify 
   rank()
   over (partition by cust
         order by rnk desc) = 1
dnoeth0
Related