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.

CodePudding user response：

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

fiddle