Given the following fruit table, I wanted to write sql query that returns Fruits that have rating of both A and B. In the example, the answer should give Orange and Lemon since they have both A & B ratings. Apple & Pear, though they have A and B rating, they should not be included in the result as they don’t contain both. One way to go about is to use stored procedure, but I wanted to write sql statement that is extensible to any number of ratings (not only A & B).
x
Fruit Rating
Orange A
Orange B
Orange C
Lemon A
Lemon B
Pear B
Pear C
Pear C
Apple A
Apple C
Expected result:
Fruit Rating
Orange A
Orange B
Lemon A
Lemon B
Advertisement
Answer
This works in Oracle. It requires support for DISTINCT in window functions.
with data (Fruit,Rating) as (
select 'Orange', 'A' from dual union all
select 'Orange', 'B' from dual union all
select 'Orange', 'C' from dual union all
select 'Lemon', 'A' from dual union all
select 'Lemon', 'B' from dual union all
select 'Pear', 'B' from dual union all
select 'Pear', 'C' from dual union all
select 'Pear', 'C' from dual union all
select 'Apple', 'A' from dual union all
select 'Apple', 'C' from dual
)
select fruit, rating
from (
select fruit, rating, cnt, max(cnt) over() max_cnt
from (
select fruit, rating,
count(distinct rating) over(partition by fruit) cnt
from data where rating in ('A','B')
)
)
where cnt = max_cnt;