Need to get the mismatcted cells in specific partition-CodePudding

ID	TC_No	Result
1	tc_1	PASS
1	tc_2	PASS
1	tc_3	FAIL
1	tc_4	PASS
1	tc_5	FAIL
2	tc_1	FAIL
2	tc_2	PASS
2	tc_3	FAIL
2	tc_4	FAIL
2	tc_5	FAIL

I'm trying to find all records that have conflicting "Result" on the same "TC_No" and among different "ID" values, filtered by ID IN (1,2).

Here's the expected output:

ID	TC_No	Result
1	tc_1	PASS
1	tc_4	PASS
2	tc_1	FAIL
2	tc_4	FAIL

and my attempted query:

SELECT * From
(SELECT * from Excel As T1
UNION 
SELECT * from Excel As T2)
As c
where ID in(1,2) order By TC_NO

CodePudding user response：

find the distinct count of result for each tc_no and then select the records having count greater than 1

Query

select * from your_tbl_name a
where exists(
  select 1 from (
    select tc_no, count(distinct result) as cnt
    from your_tbl_name 
    where result in ('PASS','FAIL')
    group by c_no
  ) b
  where a.tc_no = b.tc_no
  and b.cnt > 1
)

CodePudding user response：

You can simply INNER JOIN the table onto itself and add a predicate in the WHERE clause to return only mismatched results.

SQL:

SELECT 
  a.ID, 
  a.TC_No, 
  a.Result 
FROM 
  Excel a 
  INNER JOIN Excel b ON a.TC_No = b.TC_No 
WHERE 
  a.Result <> b.Result;

Result:

| ID | TC_No | Result |
|----|-------|--------|
|  1 |  tc_1 |   PASS |
|  1 |  tc_4 |   PASS |
|  2 |  tc_1 |   FAIL |
|  2 |  tc_4 |   FAIL |

SQL Fiddle Demo: Here

CodePudding user response：

Check when the maximum result is different than the minimum one, in your filtered data, using window functions.

WITH cte AS (
    SELECT tab.*, 
           MAX(Result_) OVER(PARTITION BY TC_No) AS max_result,
           MIN(Result_) OVER(PARTITION BY TC_No) AS min_result
    FROM tab
    WHERE ID IN (1,2)
)
SELECT Id, Tc_No, Result_
FROM cte
WHERE min_result < max_result

Check the demo here.