# Finding groups which have two values in another column

Given the following sample rows:

``````MTYPE RNO VAL
S     1   V1
S     1   V2
S     1   V3
R     1   V4
S     2   V5
S     2   V6
R     3   V7
R     3   V8
``````

How can I list groups of RNO which has both 'R' and 'S' in any of its rows in MTYPE column. In the above sample data the result should be:

``````MTYPE RNO VAL
S     1   V1
S     1   V2
S     1   V3
R     1   V4
``````

As RNO=1 group has both S and R rows in MTYPE column.

first, find the RNO that with both `R` and `S`. Then join back to your table to select the rows

``````with
rno as
(
select  RNO
from    yourtable
group by RNO
having  min(MTYPE) = 'R'
and     max(MTYPE) = 'S'
)
select  *
from    rno  r
inner join yourtable t  on  r.RNO   = t.RNO
``````
• Awesome! Thanks a lot. Sep 7, 2021 at 1:40

Instead of using an aggregate, I would suggest using INTERSECT, as in:

``````;WITH src AS
(
SELECT t.RNO
FROM #t t
WHERE t.MTYPE = 'S'
INTERSECT
SELECT t.RNO
FROM #t t
WHERE t.MTYPE = 'R'
)
SELECT t.*
FROM #t t
INNER JOIN src ON t.RNO = src.RNO;
``````

If you use `MIN()` and `MAX()` to determine the intersection of rows containing the values you want, you'll see incorrect results If there are rows in the source table containing values that are occur before `R` or after `S` in the particular collation used by the column\table\database, then the query will return incorrect results.

As an example, consider this minimal, complete, and verifiable example:

``````DROP TABLE IF EXISTS #t;

CREATE TABLE #t
(
MTYPE   char(1) NOT NULL
, RNO   tinyint NOT NULL
, VAL   char(2) NOT NULL
);

INSERT INTO #t (MTYPE, RNO, VAL)
VALUES
('Q', 1, 'V1')
, ('S', 1, 'V2')
, ('S', 1, 'V3')
, ('R', 1, 'V4')
, ('S', 2, 'V5')
, ('S', 2, 'V6')
, ('R', 3, 'V7')
, ('T', 3, 'V8');

;with
rno as
(
select  RNO
from    #t
group by RNO
having  min(MTYPE) = 'R'
and     max(MTYPE) = 'S'
)
select  *
from    rno  r
inner join #t t  on  r.RNO   = t.RNO;
``````

The output returns zero rows:

MTYPE RNO VAL

Whereas the `INTERSECT` operator returns any RNO that has both S and R `MTYPE` values:

MTYPE RNO VAL
Q 1 V1
S 1 V2
S 1 V3
R 1 V4

If your source table has a substantial number of rows, you may want to split the `INTERSECT` and the `SELECT` into two operations:

``````DROP TABLE IF EXISTS #x;

SELECT t.RNO
INTO #x
FROM #t t
WHERE t.MTYPE = 'S'
INTERSECT
SELECT t.RNO
FROM #t t
WHERE t.MTYPE = 'R'

SELECT *
FROM #x
INNER JOIN #t ON #x.RNO = #t.RNO;
``````

I'd also consider adding the following index to the table:

``````CREATE INDEX t_MTYPE
ON #t (MTYPE, RNO)
INCLUDE (VAL);
``````
• Thanks Hannah, I find this one good because I can extend it for say three values in the MTYPE column. I wish I can mark multiple answers as solution to my problem. Sep 7, 2021 at 2:05