Prioritize SELECT based on the condition

Database

Prioritize SELECT based on the condition

Hi All,

I have a table which has the below contents:

123    BC

123    PQ

123    AB

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?

Thanks,

Ajit

1 REPLY
Senior Apprentice

Re: Prioritize SELECT based on the condition

select * from tab
qualify
rank()
over (partition by col1 -- probably needed
order by case col2
when 'BC' then 1
when 'PQ' then 2
else 3
end) = 1