Select row if col1 value exits twice and col2 is not null
We are monitoring network devices. A device may appear at more than one switch.
We want to filter out those devices which are on an uplink / portchannel in case it appears also on another port. All other devices are selected.
Let's say the table looks like:
HOST, SWITCH, PORT HostA, Switch1, 01 HostB, Switch1, 02 HostA, Switch2, Po - Po is portchannel / uplink HostC, Switch2, Po - Po is portchannel / uplink
HostA, Switch1, 01 HostB, Switch1, 02 HostC, Swtich2, Po - is only on an uplink / so that is OK
The Entry HostA, Switch2, Po needs to be filtered out since it appears on another port as well.
Now the question is how to write an efficient query.
In SQL terms we want to select all rows except those where HOST appears twice. Then we want only that row where PORT is not 'Po'
Our current query is slow because of subqueries !? I assume that the subquery is creating a cartesian product - right?
SELECT * FROM devices t1 WHERE NOT ((Port = 'Po') AND ((Select count(*) from table t2 where t1.host=t2.host AND NOT Port='Po') > 0))
Again the question is how to write a faster SQL query??
Not exists should be faster than correlated count(*)
select * from devices t1 where (port <> 'Po' or not exists (select null from devices t2 where t2.host = t1.host and t2.Port <> 'Po') )
UPDATE: if you think that join version would perform better:
select t1.* from devices t1 left join devices t2 on t1.host = t2.host and t2.port <> 'Po' where (t1.port <> 'Po' or t2.port is null)
The query self-joines devices table, removing Po's from second instance. It then proceeds to select all non-Po's and Po's without matching non-Po. Oh well, what an explanation.
SELECT HOST as HOST, SWITCH, PORT from table WHERE port<>'po' UNION ALL SELECT MAX(HOST) as HOST, SWITCH, PORT from table WHERE port='po' GROUP BY SWITCH, PORT
Assuming there is at most one port on a host/switch combination, you can use group by:
select HOST, SWITCH, (case when max(case when PORT <> 'Po' then 1 else 0 end) = 1 then max(case when PORT <> 'Po' then port end) else 'Po' end) port from t group by host, switch
If you have a database that supports windows functions, you can use:
select t.host, t.switch, t.port from (select t.*, sum(isPo) over (partition by host, switch) as numPo, count(*) over (partition by host, switch) as numAll from (select t.*, (case when port = 'po' then 1 else 0 end) as isPo from t ) t ) t where numPo = numAll or -- all are Po (port <> 'Po') -- remove any other Pos