Hello,

my first query "select Barcode as sample_barcodes" returns the following entries:

-----------------
SAMPLES_BARCODES
50027
50028
50029
-----------------

my second query "select CampaignItemId, Barcode from MyView" returns the following entries:

-------------------------
CampaignItemId   BarCode
item-custom-campaign-1 1
item-custom-campaign-1 2
item-custom-campaign-1 3
item-custom-campaign-1 4
item-custom-campaign-1 5
item-custom-campaign-1 6
item-custom-campaign-1 50028
item-custom-campaign-1 8
item-custom-campaign-1 9
item-custom-campaign-1 10
item-custom-campaign-1 7
item-custom-campaign-3 1
item-custom-campaign-3 2
item-custom-campaign-3 3
item-custom-campaign-3 4
item-custom-campaign-3 5
item-custom-campaign-3 6
item-custom-campaign-3 8
item-custom-campaign-3 9
item-custom-campaign-3 10
item-custom-campaign-3 7
-------------------------

What i want to achive is the following : For each campaignItemId (generated by the second query), select only the first barcode that has match in the first query("SAMPLES_BARCODES") or the first barcode under it in case there is no match between the Barcode(second query) and the "SAMPLES_BARCODES"(first query)

So i want my result to look like this

-----------------
item-custom-campaign-1 50028 (this entry has a match in SAMPLES_BARCODES)
item-custom-campaign-3 1 (this entry does not have a match in SAMPLES_BARCODES)
-----------------

Is this possiblle ? Also it is good to keep in mind that this is just an example and in real life i may have up to 1 million entries in each table.

Thank you

with samples as (
  select 100 s from dual union all
  select 101 s from dual union all
  select 102 s from dual
), items as (
  select 1 item, 1 code from dual union all
  select 1 item, 2 code from dual union all
  select 1 item, 100 code from dual union all
  select 2 item, 1 code from dual union all
  select 2 item, 2 code from dual union all
  select 2 item, 3 code from dual 
), item_samples as (
  select i.*,
         first_value(
           code
         ) over (partition by item order by s, code) fv 
  from   items i
  left   join samples s
  on     i.code = s.s
)
  select * from item_samples
  where  code = fv;

ITEM   CODE   FV    
     1    100   100 
     2      1     1