Return tuples if and only if the rest of the tuple all match-CodePudding

My goal is to output a table that contains objects which their Data1 and Data2 pairs must all be equal. For example, in tables below, A is in two tuples where 2=2 and 1=1, and there does not exist a tuple for A that Data1 <> Data2. B has 4=4 however 3 <> 1 so it will not be output. I can't simply use WHERE Data1=Data2 otherwise | B | 4 | 4 | will be included as well.

SELECT * FROM Table

Obj	Data1	Data2
A	2	2
A	1	1
B	3	1
B	4	4
C	5	6
C	7	8

Expected result:

Obj	Data1	Data2
A	2	2
A	1	1

CodePudding user response：

Try this:

with u as 
(select Obj, 
Data1, 
Data2,
sum(case when Data1 <> Data2 then 1 else 0 end) over(partition by Obj) as r
from table_name)
select Obj, Data1, Data2 from u
where r = 0

Fiddle

Basically using the sum window function with a case expression to get r the number of times Data1 <> Data2 for each Obj, then only selecting rows with r = 0

CodePudding user response：

WITH CTE(Obj,   Data1 , Data2) AS
(
  SELECT 'A' ,  2 , 2 UNION ALL
  SELECT 'A' ,  1 , 1 UNION ALL
  SELECT 'B' ,  4 , 4 UNION ALL
  SELECT 'C' ,  5 , 6 UNION ALL
  SELECT 'C' ,  7 , 8 UNION ALL
  SELECT 'B' ,    3,  1 UNION ALL
  SELECT 'C' ,  5 , 6 UNION ALL
  SELECT 'C' ,  7 , 8
)
 SELECT C.Obj,C.Data1,C.Data2
 FROM CTE AS C
 WHERE NOT EXISTS
 (
   SELECT 1 FROM CTE AS X
  WHERE C.Obj=X.Obj AND X.Data1<>X.Data2
 )