hi all, consider the following data
with data as
(
select 'KPT' pol_id, 'EM' pol_name from dual union all
select 'KPT' pol_id, 'ME' pol_name from dual union all
select 'MCA' pol_id, 'ME' pol_name from dual
),
data2 as
(
select 'KPT' pol_id from dual union all
select 'MCA' pol_id from dual
)
i am trying to join both data and data2 tables. i would like to produce the following output
pol_id pol_name
=================
KPT EM
MCA ME
i am able to do the joining part. however, i want to pick rows base on a condition. for example, if pol_id is the same value then check to see if there is a row with value as EM in pol_name.
if there is a row, then pick that one and discard the rest. if there is no row with EM then pick row with ME.
for example, for pol_id=KPT we can see there are two rows one with pol name as EM and other ME in data table. we pick the one with EM and discard the other and join with data2 which produce the first row in the output.
for pol id=MCA, we first look for any row with pol name=EM in data table. there is none, then we search for any row with ME. there is one. pick that one and join with data2 table.
i was trying to use dense rank and do a case statement to check if any rows has EM value but i was not successful.
can someone help write a query that produce result above based on the logic explained above?
thanks