Select rows with the same value in one column and specific values in another column

Question

For this table structure:

create table devices (
  id  number,
  serial number(10,0),
  location varchar2(2),
  size varchar2(2)
)

and data:

insert into devices values (1, 1111, 'Q2', 'SM');
insert into devices values (2, 2222, 'L0', 'SM');
insert into devices values (3, 3333, 'AB', 'MD');
insert into devices values (4, 1111, 'N4', 'LG');
insert into devices values (5, 3333, 'AB', 'SM');
insert into devices values (6, 4444, 'F4', 'LG');
insert into devices values (7, 5555, 'Q9', 'SM');
insert into devices values (8, 4444, 'G1', 'SM');
insert into devices values (9, 1111, 'H1', 'MD');

I need to select all the rows with identical serial values that have a 'size' value of 'SM' and 'LG'.

So, the results should be:

| ID | SERIAL | LOCATION | SIZE |
|----|--------|----------|------|
|  1 |  1111  |   Q2     |  SM  |
|  4 |  1111  |   N4     |  LG  |
|  6 |  4444  |   F4     |  LG  |
|  8 |  4444  |   G1     |  SM  |

I swear I've done something similar before but cannot find that code and it's driving me nuts!

Answer 1

Quickly, here is one way to do it. There may be better:

with aa as
         (select serial,
                 row_number() over (
                     partition by serial
                     ) as rn
          from devices
          where size = 'SM'
             or size = 'LG'),
     bb as (select *
            from aa main
            where main.rn <> 1)
select jj.*
from bb
join devices jj on (jj.serial = bb.serial)
where size = 'SM'
   or size = 'LG';

Output:

+--+------+--------+----+
|id|serial|location|size|
+--+------+--------+----+
|1 |1111  |Q2      |SM  |
|4 |1111  |N4      |LG  |
|6 |4444  |F4      |LG  |
|8 |4444  |G1      |SM  |
+--+------+--------+----+