Select a query to retrieve rows matching all the values ​​in the column

Question

Consider this sample table "Table 1".

Col1   Col2
 A      1
 B      1
 A      4
 A      5
 A      3
 A      2
 D      1
 B      2
 C      3
 B      4

I am trying to extract these values ​​from Col1, which matches all the values ​​(in this case 1,2,3,4,5). Here, the query result should return “A”, since none of the others has all the values ​​1,2,3,4,5 in Col2.

Note that the values ​​in Col2 are determined by other parameters in the query, and they will always return some numerical values. From these values, the query should retrieve the values ​​from Col1 that match all in Col2. The values ​​in Col2 may be, for example, 11,12,1,2,3,4 (which does not necessarily mean in sequence).

I tried the following select query:

select distinct Col1 from Table1 where Col1 in (1,2,3,4,5);

select distinct Col1 from Table1 where Col1 exists (select distinct Col2 from Table1);

. , "" Col2, "".

like Col1, Col2 ' 1 5.

.

Answer 1

ROW_NUMBER().

SQL FIddle .

SELECT col1
FROM
  (SELECT col1,
    col2,
    row_number() OVER(PARTITION BY col1 ORDER BY col2) rn
  FROM your_table
  WHERE col2 IN (1,2,3,4,5)
  )
WHERE rn =5;

---

OP, , .

:

SQL> SELECT col1,
  2    col2,
  3    row_number() OVER(PARTITION BY col1 ORDER BY col2) rn
  4  FROM t
  5  WHERE col2 IN (1,2,3,4,5);

C       COL2         RN
- ---------- ----------
A          1          1
A          2          2
A          3          3
A          4          4
A          5          5
B          1          1
B          2          2
B          4          3
C          3          1
D          1          1

10 rows selected.

PARTITION BY col1, ORDER BY col2 col1. , row_number . , , row_number 5. , , , WHERE rn =5 .

Answer 2

listagg,

SELECT Col1
FROM
(select Col1,listagg(Col2,',')  within group (order by Col2) Col2List  from Table1
group by Col1)
WHERE Col2List = '1,2,3,4,5'

Answer 3

You can also use below

SELECT COL1
    FROM TABLE_NAME
    GROUP BY COL1
    HAVING 
    COUNT(COL1)=5
    AND 
    SUM(
    (CASE WHEN COL2=1 THEN 1 ELSE 0
    END)
    +
    (CASE WHEN COL2=2 THEN 1 ELSE 0
    END)
    +
    (CASE WHEN COL2=3 THEN 1 ELSE 0
    END)
    +
    (CASE WHEN COL2=4 THEN 1 ELSE 0
    END)
    +
    (CASE WHEN COL2=5 THEN 1 ELSE 0
    END))=5