Finding groups which have two values in another column

group byquerysql serversql-server-2008

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.

Best Answer

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
Related Question