SQL query to limit results based on Priority value

Question

I am looking for some help with the following task. I am selecting records from MY_TABLE having ASSET_TYPE existing in mapping table MAP_RECRF_ASSET_TYPE but I am getting duplicated TECHNICAL_IDs.

For this purpose I have added PRIORITY column in MAP_RECRF_ASSET_TYPE and in the results I would like to have only one record for each TECHNICAL_ID having ASSET_TYPE from MAP_RECRF_ASSET_TYPE table but also with the lowest value of PRIORITY (there will always be only 1 record for the same value of PRIORITY).

My DB is Oracle 19c.

Would greatly appreciate helping me with this code. Thanks

My current query:

with MY_TABLE as (
select '1111' as TECHNICAL_ID, 'NOTIONALCR' as ASSET_TYPE from dual union all
select '1111' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '2222' as TECHNICAL_ID, 'FWDNOTLCR'  as ASSET_TYPE from dual union all
select '2222' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '3333' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '3333' as TECHNICAL_ID, 'DUMMY'      as ASSET_TYPE from dual
),

MAP_RECRF_ASSET_TYPE as (
select 'SW' as APPLICATION, 'NOTIONALCR' as ASSET_TYPE, 1 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'NOTIONALDB' as ASSET_TYPE, 1 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'FWDNOTLCR'  as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'FWDNOTLDR'  as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWOFFBALCR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWOFFBALDR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWFWNOTLCR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWFWNOTLDB' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, '50000'      as ASSET_TYPE, 3 as PRIORITY from dual
)
                SELECT *
                FROM MY_TABLE x
                WHERE
                    x.ASSET_TYPE IN (
                        SELECT ASSET_TYPE
                        FROM MAP_RECRF_ASSET_TYPE
                        WHERE APPLICATION = 'SW');

-- Current results:

TECHNICAL_ID    ASSET_TYPE
1111    NOTIONALCR
1111    50000
2222    FWDNOTLCR
2222    50000
3333    50000

-- Expected results:

TECHNICAL_ID    ASSET_TYPE
1111    NOTIONALCR
2222    FWDNOTLCR
3333    50000

Answer 1

you can add another CTe where you join both tables, it would select one of the priorities and add a row nmuber sorted by the priority

with MY_TABLE as (
select '1111' as TECHNICAL_ID, 'NOTIONALCR' as ASSET_TYPE from dual union all
select '1111' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '2222' as TECHNICAL_ID, 'FWDNOTLCR'  as ASSET_TYPE from dual union all
select '2222' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '3333' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '3333' as TECHNICAL_ID, 'DUMMY'      as ASSET_TYPE from dual
),

MAP_RECRF_ASSET_TYPE as (
select 'SW' as APPLICATION, 'NOTIONALCR' as ASSET_TYPE, 1 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'NOTIONALDB' as ASSET_TYPE, 1 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'FWDNOTLCR'  as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'FWDNOTLDR'  as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWOFFBALCR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWOFFBALDR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWFWNOTLCR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWFWNOTLDB' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, '50000'      as ASSET_TYPE, 3 as PRIORITY from dual
), CTE AS (
 
                SELECT x.TECHNICAL_ID,x.ASSET_TYPE,
  ROW_NUMBER() OVER(PARTITION BY x.TECHNICAL_ID ORDER BY mrat.PRIORITY) rn
                FROM MY_TABLE x JOIN MAP_RECRF_ASSET_TYPE mrat 
  ON x.ASSET_TYPE = mrat.ASSET_TYPE
                WHERE 
                   mrat.APPLICATION = 'SW')
SELECT TECHNICAL_ID,ASSET_TYPE FROM CTE WHERE rn = 1
  

TECHNICAL_ID	ASSET_TYPE
1111	NOTIONALCR
2222	FWDNOTLCR
3333	50000

fiddle