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!
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 |
+--+------+--------+----+