Ignore records base by criteria

1

Hello,

I need a query to... Ignore records if

  1. there are no duplicate values in column one.
  2. column one has duplicate values and column two has duplicate values

Keep duplicate records if
3) column one has duplicate values and column two has at least one mismatch value

Create table TB1
(
SON INT, DELN INT, INN INT
)

INSERT INTO TB1 Values
('343', '1751', '5555'),
('751', '2345', '6666'),
('751', '2345', '7777'),
('125', '2100', '1111'),
('125', '2112', '1118'),
('223', '1133', '4545'),
('223', '1133', '8888'),
('223', '1133', '5666'),
('555', '2033', '1010'),
('555', '2033', '1800'),
('555', '2074', '1001')

SELECT * FROM TB1

SON	DELN	INN
343	1751	5555 -- ignore
751	2345	6666 -- ignore
751	2345	7777
125	2100	1111 -- keep 
125	2112	1118 
223	1133	4545 -- ignore
223	1133	8888
223	1133	5666
555	2033	1010 -- keep 
555	2033	1800 
555	2074	1001

After running your query, the result would be.

SON	DELN	INN
125	2100	1111
125	2112	1118
555	2033	1010
555	2033	1800
555	2074	1001

I would really appreciate if you could help.

2

Not sure if the following is quite the logic, but something like that is what you should do. Modify the final where clause if this is not giving you the right answers.

;WITH cte AS
(
	SELECT *,
		COUNT(*) OVER(PARTITION BY SON)-
		COUNT(*) OVER(PARTITION BY SON, DELN) AS N3
	FROM
		TB1
)
SELECT * FROM
cte c1 
	WHERE N3 <> 0;
3

Hi James,

Your query works great. But it adds an extra column (N3). How to get rid of that column.

SON	DELN	INN	N3
125	2100	1111	1
125	2112	1118	1
555	2033	1010	1
555	2033	1800	1
555	2074	1001	2

Thanks.

4

Hi James,

I just figured it out...

Thank you.