How to remove rows with different values within the same option value-CodePudding

In SQL, can someone help me understand what the query may look like if I'm trying to remove the red-highlighted rows from the image? Based on the logic I need, I need to remove records with different priority values within an option, where as the ones we want to keep have the same priority. Each item/cntry combination has it's own option values (typically 4 like you see here).

I feel like this is just a join to itself, but my mind is mush right now. Help would be appreciated!

CodePudding user response：

use exists and distinct count()

select t1.* from table_name t1 where
exists ( 
select 1
from table_name t2
where t1.option=t2.option
group by option
having count (distinct priority)=1
)

CodePudding user response：

You can use the analytic COUNT(DISTINCT ...) function and then you do not need to use a self-join:

SELECT *
FROM   (
  SELECT t.*,
         COUNT(DISTINCT priority) OVER (PARTITION BY cntry, item, "OPTION")
           AS cnt
  FROM   table_name t
)
WHERE  cnt = 1;

Which, for the sample data:

CREATE TABLE table_name ("OPTION", priority, item, cntry) AS
SELECT 1, 1, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 1, 1, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 2, 2, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 2, 1, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 3, 1, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 3, 2, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 4, 2, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 4, 2, 'ABCDEF', 'USA' FROM DUAL UNION ALL
SELECT 1, 1, 'HIJKLM', 'CAN' FROM DUAL UNION ALL
SELECT 2, 2, 'HIJKLM', 'CAN' FROM DUAL UNION ALL
SELECT 2, 2, 'HIJKLM', 'CAN' FROM DUAL UNION ALL
SELECT 2, 1, 'HIJKLM', 'CAN' FROM DUAL UNION ALL
SELECT 3, 1, 'HIJKLM', 'CAN' FROM DUAL UNION ALL
SELECT 3, 1, 'HIJKLM', 'CAN' FROM DUAL UNION ALL
SELECT 3, 1, 'HIJKLM', 'CAN' FROM DUAL UNION ALL
SELECT 4, 2, 'HIJKLM', 'CAN' FROM DUAL;

Outputs:

OPTION PRIORITY ITEM CNTRY CNT

1 1 HIJKLM CAN 1

3 1 HIJKLM CAN 1

3 1 HIJKLM CAN 1

3 1 HIJKLM CAN 1

4 2 HIJKLM CAN 1

1 1 ABCDEF USA 1

1 1 ABCDEF USA 1

4 2 ABCDEF USA 1

4 2 ABCDEF USA 1

OPTION	PRIORITY	ITEM	CNTRY	CNT
1	1	HIJKLM	CAN	1
3	1	HIJKLM	CAN	1
3	1	HIJKLM	CAN	1
3	1	HIJKLM	CAN	1
4	2	HIJKLM	CAN	1
1	1	ABCDEF	USA	1
1	1	ABCDEF	USA	1
4	2	ABCDEF	USA	1
4	2	ABCDEF	USA	1

db<>fiddle here