Checking for a one-to-one correlation of data with MySQL

user_187581 0
Viewed 26

Suppose I have data containing two columns I am interested in. Ideally, the data in these is always in matching sets like this:

A 1
A 1
B 2 
B 2
C 3
C 3
C 3

However, there might be bad data where the same value in one column has different values in the other column, like this:

D 4 
D 5

or:

E 6
F 6

How do I isolate these bad rows, or at least show that some of them exist?

mysql sql
2 Answers

Using MIN and MAX as analytic functions we can try:

WITH cte AS (
    SELECT t.*, MIN(col2) OVER (PARTITION BY col1) AS min_col2,
                MAX(col2) OVER (PARTITION BY col1) AS max_col2
    FROM yourTable t
)

SELECT col1, col2
FROM cte
WHERE min_col2 <> max_col2;

The above approach, while seemingly verbose, would return all offending rows.

user_18632290

You can use exists:

select t.*
from t
where exists (select 1 from t t2 where t2.col1 = t.col1 and t2.col2 <> t.col2);

If you just want the col1 values that have non-matches, you can use aggregation:

select col1, min(col2), max(col2)
from t
group by col1
having min(col2) <> max(col2);
user_11440350
Related