Link to home
Start Free TrialLog in
Avatar of Errol Farro
Errol FarroFlag for Aruba

asked on

MS SQL omit paired codes

In a table, code 2735 and 4649 are paired code. 


Code 4649 can only be in the table if there is an entry for 2735


When there is an entry for both 2735 and 4649, those two rows must be omitted from the SQL


Any help is greatly appreacited




Example 1

=========


Code

2735

1545

4649

4152


Result

======

1545

4152



Example II

==========

1669

4158

2735

4649

2735

2735


Result

======

1669

4158

2735

2735


Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

If the Codes will only appear as pairs, then simply this should work:


SELECT Code

FROM dbo.table_name

WHERE Code NOT IN ('2735', '4649')

Avatar of Errol Farro

ASKER

2735 can appear as a single row as well
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

See if this works for you:
with cte as (
	select code,
		count(case when code=2735 then 1 end) over() count_2735,
		count(case when code=4649 then 1 end) over() count_4649,
		case when code=2735 then row_number() over(partition by code order by code) end rn_2735,
		case when code=4649 then row_number() over(partition by code order by code) end rn_4649
	from mytable
)
select code
from cte
where 
		rn_2735 <= count_2735 - count_4649 or (rn_2735 is null and rn_4649 is null)

Open in new window

select code
from tablename
where code not in ('2735', '4649')
Union
Select case when dif>1 then '2735' else '4649' form
(select sum(case when code='2735' then 1 when code='4649' then -1 else 0) dif from  tablename
where code in ('2735', '4649')) where dif<>0
@Errol,
The solution you accepted does not return the results you said you wanted for the second example.

At least for me:
https://dbfiddle.uk/xQGePg-w