Nrthugu

Not sure how to title this (therefore didn't get lucky when trying to search for an answer), but let's say I've got a table as follows:

Product Quality Source

P1      0.80    Provider1

P1      0.75    Provider2

P1      0.20    Provider3

P1      0.83    Provider4

P1      0.60    Provider5

P2      0.45    Provider1

P2      0.76    Provider2

P2      0.20    Provider3

P2      0.87    Provider4

P3      0.45    Provider1

P3      0.30    Provider2

P3      0.84    Provider4

P3      0.24    Provider5

and I only want to keep products that are either Provider1 or Provider3 or has a higher quality than the best of qualities of that product on Provider1 or Provider3. So I'd like to end up with:

Product Quality Source

P1      0.80    Provider1

P1      0.20    Provider2

P1      0.83    Provider4

P2      0.45    Provider1

P2      0.76    Provider2

P2      0.20    Provider3

P2      0.87    Provider4

P3      0.45    Provider1

P3      0.84    Provider4

I've got quite a bit of data so I'm looking for a smart way to do this.

Brute force would be something like:

select * from mytable m1 where m1.Source = 'Tier1' or m1.Source > (select max(m2.Quality) from mytable m2 where m2.Product = m1.Product and m2.Source = 'Tier1')

Any suggestion?

Thanks

Edit1: I had simplified the problem slightly by omitting that each product could have multiple sources, I've now amended my post.

Using Littlefoot's suggestion:

with inter as 

    (

            select m1.product, m1.quality, m1.source,

                    (select max(m2.quality) from mytable m2 

                            where m2.product = m1.product 

                            and m2.source in ('Provider1', 'Provider3')

                    ) max_quality

            from mytable m1

    )

select product, quality, source from inter

    where source in ('Provider1', 'Prodiver3') 

    or quality > max_quality;

I will need to see if that is fast enough when I run the request on the whole table (~hundreds of thousands of rows).

One option might be

SQL> with test (product, quality, source) as

  2    (select 'P1', 0.8,  'Tier1' from dual union all

  3     select 'P2', 0.75, 'Tier2' from dual union all

  4     select 'P3', 0.2,  'Tier2' from dual union all

  5     select 'P4', 0.83, 'Tier2' from dual union all

  6     select 'P5', 0.6,  'Tier1' from dual

  7    ),

  8  inter as

  9    (select product, quality, source,

 10      (select max(quality) max_qual from test

 11       where source = 'Tier1'

 12      ) max_qual

 13     from test

 14    )

 15  select product, quality, source

 16  From inter

 17  where source = 'Tier1'

 18    or quality > max_qual;



PR    QUALITY SOURC

-- ---------- -----

P1         ,8 Tier1

P4        ,83 Tier2

P5         ,6 Tier1



SQL>

Or:

  <snip>

  8  inter as

  9    (select product, quality, source,

 10      max(case when source = 'Tier1' then quality else 0 end) over (order by null) max_qual

 11     from test

 12    )

 13  select product, quality, source

 14  from inter

 15  where source = 'Tier1'

 16    or quality > max_qual;



PR    QUALITY SOURC

-- ---------- -----

P1         ,8 Tier1

P4        ,83 Tier2

P5         ,6 Tier1



SQL>