sqloraclegreatest-n-per-grouporacle19c

SQL query to limit results based on Priority value


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

Solution

  • 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