0

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.

0
0

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
1
  • Awesome! Thanks a lot.
    – Gurpreet
    Sep 7 '21 at 1:40
2

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);
1
  • 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.
    – Gurpreet
    Sep 7 '21 at 2:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.