Selecting ranked records with case statement

Table: Sales

The above Sales table is already ranked based on Cost in descending order.

I'd like to select each single Cust and their corresponding Product and Cost based on RN = 1 except if the RN = 1 has Product = TBD, then select the RN = 2 record per Cust. An additional condition is that if the Cust only has RN = 1and Product = TBD then that particular record should flow through.

I'm honestly not too sure how to condition my query due to the special condition.

I've tried:

select * 
from Sales
where RN = (case when (Product = 'TBD' and RN = 1 and Product <> 'TBD' and RN> 1) then 2 else 1 end)

This doesn't return the intended result as it always shows product = 'TBD'

I think I would need to apply aggregation at Cust level, apply filter to check if record RN = 1 has Product = 'TBD', then manually assign a lower rank number and in final output, select records where RN = 1.

Hoping someone can provide some guidance.

One option uses window functions identify the top record per group; we can represent the logic with a case expression that de-prioritize the "first" record if it has the TBD product.

select *
from (
    select s.*,
        row_number() over(
            partition by cust 
            order by case when rn = 1 and product = 'TBD' then 1 else 0 end, rn
        ) seq
    from sales s
) where seq = 1

select distinct
    Cust,
    first_value(Product) over (partition by Cust order by case when Product = 'TBD' then 1 else 0 end, RN) as Prod,
    first_value(Cost)    over (partition by Cust order by case when Product = 'TBD' then 1 else 0 end, RN) as Cost
from T;

This is essentially the same as the other answer though a distinct operation may change the query plan.

If you can guarantee that exactly one of the first two rows contains what you want:

with data as (
    select *,
        count(*) over (partition by Cust) as RowCnt
    from T
    where RN <= 2
)
select
from data
where RowCnt = 1 or Product <> 'TBD';

If you don't have analytic functions available:

select * from T t1
where RN = 1 and Product = 'TBD'
    or RN = (
        select min(RN) from T t2
        where t2.Cust = t1.Cust and Product <> 'TBD'
    );

SELECT *
FROM (
  SELECT 
    Cust,
    Product,
    Cost,
    CASE
      WHEN RN = 1 AND Product = 'TBD' THEN 0  -- Assign a lower rank for Product = 'TBD' with RN = 1
      ELSE RN
    END AS NewRN
  FROM Sales
) AS subquery
WHERE NewRN = 1;

This query creates a subquery that assigns a new rank number (NewRN) based on the existing RN and the condition for Product = 'TBD' with RN = 1. Then, in the outer query, it selects the records where the new rank number (NewRN) is 1.

I've been tinkering with the logic and while it's not in one sql query and probably not the most efficient or clean I've been able to nail down the intended result. Open to suggestions on how I could improve upon this,

--identify cust where product = 'TBD' and RN =1

With ab as (
select distinct cust, RN from sales
where product = 'TBD' and RN = 1'
),

--identify for those cust with product = 'TBD' and RN =1, how many have more than 1 cust (meaning more than just product = 'TBD')

bc as (
select cust, count(*) from sales
where cust = (select cust from ab)
group by 1 having count(*)>1),

--select from the sales table excluding cust which are product = 'TBD' and RN =1 and have count of cust greater than 1

final1 as (
select * from sales
where cust not in (select cust from bc)),

--select from the sales table but look for the cust which are product = 'TBD' and RN =1 BUT select the RN = 2 record so avoid selecting the first record with product = 'TBD' and RN =1

final2 as (
select * from sales
where cust in (select cust from bc)
where RN = 2)

--combine everything together

select * from final1
union
select * from final2