Deena Deena - 1 month ago 4
SQL Question

SQL query that finds repeated data in one column with different data in a second column

Let's say I have data that looks like this:

ID Date Data
A D1 123
A D1 456
A D2 123


What I'm looking for is a select statement that will pull all rows where ID and Date are repeated as a pair, but the data doesn't match. In this case, it would return the top two rows, because ID A and Date D1 are repeated as a pair, but the Data is different. The third row would not be returned because the ID and Date combination are not repeated.

Answer

There are several ways to do this. Here's one option using exists:

select *
from yourtable t
where exists (
    select 1
    from yourtable t2
    where t.id = t2.id and t.date = t2.date and t.data <> t2.data
)
Comments