how to find the value which is common in a column for all the values in other column?

Question

If i'm having a table like this.

col1    col2   col3
1       2       3
1       3       2
1       2       1
1       2       2
1       2       3

I want only the col2 values which appears for all three values(1,2,3) in col3

col2
2

How to get result like this guys?

Answer 1

Assuming that 1, 2 and 3 are the only acceptable values for col3 (and that the column is not null), then you could do it like this:

with sample_data as (select 1 col1, 2 col2, 3 col3 from dual union all
                     select 1 col1, 3 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 1 col3 from dual union all
                     select 1 col1, 2 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 3 col3 from dual),
             res as (select col1,
                            col2,
                            col3,
                            count(distinct col3) over (partition by col1, col2) cnt_distinct_col3
                     from   sample_data)
select col1,
       col2,
       col3
from   res
where  cnt_distinct_col3 = 3;

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          1
         1          2          2
         1          2          3
         1          2          3

I wasn't sure from your sample_data if you were just going from col2, or whether it was col1 + col2; eg, if the sample data was:

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3
         1          3          2
         1          2          1
         1          2          2
         1          2          3
         2          3          1
         2          3          3

Would you expect to see col2 = 2 and col2 = 3 rows returned, or would you only expect to see (col1, col2) = (1, 2) rows returned?

I have assumed the latter, but if it's the former, it's just a case of taking col1 out of the partition clause in the analytic function. And then, of course, changing the select to "select distinct col2".

---

So, depending on how you want the answers displayed, I think my query works for the situations where you want to show col1 in the results (ie. unfiltered) and when you want to filter by a specific col1 value, e.g;

with sample_data as (select 1 col1, 2 col2, 3 col3 from dual union all
                     select 1 col1, 3 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 1 col3 from dual union all
                     select 1 col1, 2 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 3 col3 from dual union all
                     select 2 col1, 3 col2, 1 col3 from dual union all
                     select 2 col1, 3 col2, 2 col3 from dual union all
                     select 2 col1, 3 col2, 3 col3 from dual union all
                     select 2 col1, 2 col2, 1 col3 from dual union all
                     select 3 col1, 1 col2, 2 col3 from dual union all
                     select 3 col1, 1 col2, 3 col3 from dual),
             res as (select col1,
                            col2,
                            col3,
                            count(distinct col3) over (partition by col1, col2) cnt_distinct_col3
                     from   sample_data)
select distinct col1,
                col2
from   res
where  cnt_distinct_col3 = 3;

      COL1       COL2
---------- ----------
         2          3
         1          2

with sample_data as (select 1 col1, 2 col2, 3 col3 from dual union all
                     select 1 col1, 3 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 1 col3 from dual union all
                     select 1 col1, 2 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 3 col3 from dual union all
                     select 2 col1, 3 col2, 1 col3 from dual union all
                     select 2 col1, 3 col2, 2 col3 from dual union all
                     select 2 col1, 3 col2, 3 col3 from dual union all
                     select 2 col1, 2 col2, 1 col3 from dual union all
                     select 3 col1, 1 col2, 2 col3 from dual union all
                     select 3 col1, 1 col2, 3 col3 from dual),
             res as (select col1,
                            col2,
                            col3,
                            count(distinct col3) over (partition by col1, col2) cnt_distinct_col3
                     from   sample_data)
select distinct col2
from   res
where  cnt_distinct_col3 = 3
and    col1 = 1;

      COL2
----------
         2

---

Ok, I think this is what you're after:

with sample_data as (select 1 col1, 2 col2, 3 col3 from dual union all
                     select 1 col1, 3 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 1 col3 from dual union all
                     select 1 col1, 2 col2, 2 col3 from dual union all
                     select 1 col1, 2 col2, 3 col3 from dual union all
                     select 2 col1, 3 col2, 1 col3 from dual union all
                     select 2 col1, 3 col2, 2 col3 from dual union all
                     select 2 col1, 3 col2, 3 col3 from dual union all
                     select 2 col1, 2 col2, 1 col3 from dual union all
                     select 3 col1, 1 col2, 2 col3 from dual union all
                     select 3 col1, 1 col2, 3 col3 from dual),
             res as (select col1,
                            col2,
                            col3,
                            count(distinct col3) over (partition by col1, col2) cnt_distinct_col3_per_col1col2,
                            count(distinct col3) over (partition by col1) cnt_distinct_col3_per_col1
                     from   sample_data)
select distinct col2
from   res
where  cnt_distinct_col3_per_col1col2 = cnt_distinct_col3_per_col1
and    col1 = 1;

      COL2
----------
         2

The way this works is:

1. find the number of distinct col3 values for each col1 value. (So in the example above, for col1 = 1, there are 3 different col3 values.)
2. find the number of distinct col3 values for each (col1, col2) pairing. (So in the example above, for (col1, col2) = (1, 2) there are 3 different col3 values, and for (col1, col2) = (1, 3) there is only 1)
3. select the rows where these two numbers match. (So from the above example, only (col1, col2) = (1, 2) has the same number of distinct values (3) as col1 = 1 does (3).

Answer 2

select col2
from your_table
group by col2
having count(distinct col3) = (select count(distinct col3) from your_table)