I have a table which has the below contents:
when we have all the above three rows containing BC,PQ and AB '123 BC' should be selected; when we have only PQ and AB(BC is not there), row '123 PQ' should be selected; when we have only AB(BC and PQ are not present), row '123 AB' should be selected.
Can anybody let me know how to accomplish this in Teradata?
select * from tab
over (partition by col1 -- probably needed
order by case col2
when 'BC' then 1
when 'PQ' then 2
end) = 1