CODEWITHSUNDEEP
sqloracle-database
Hemang
Hemang

Reputation: 410

select query to fetch rows corresponding to all values in a column

Consider this example table "Table1". 

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 fetch those values from Col1 which corresponds to all values (in this case, 1,2,3,4,5). Here the result of the query should return 'A' as none of the others have all values 1,2,3,4,5 in Col2.

Note that the values in Col2 are decided by other parameters in the query and they will always return some numeric values. Out of those values the query needs to fetch values from Col1 corresponding to all in Col2. The values in Col2 could be 11,12,1,2,3,4 for instance (meaning not necessarily in sequence).

I have 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);

and its different variations. But the problem is that I need to apply an 'and' for Col2 not an 'or'.

like Return a value from Col1 where Col2 'contains' all values between 1 and 5.

Appreciate any suggestion.

Upvotes: 4

Views: 829

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could use analytic ROW_NUMBER() function.

SQL FIddle for a setup and working demonstration.

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;

UPDATE As requested by OP, some explanation about how the query works.

The inner sub-query gives you the following resultset:

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 clause will group each sets of col1, and ORDER BY will sort col2 in each group set of col1. Thus the sub-query gives you the row_number for each row in an ordered way. now you know that you only need those rows where row_number is at least 5. So, in the outer query all you need ot do is WHERE rn =5 to filter the rows.

Upvotes: 1

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

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

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

You can use listagg function, like

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'

Upvotes: 1

Related Questions