CODEWITHSUNDEEP
sqlsql-server
Sam
Sam

Reputation: 63

How can I find rows that have two columns that are the same, but one column differs?

I have a database with a bunch of differnt tables that are joined together to create the following table:

Col1   Col2    Col3           Col4
51     1       1101    2009-01-24 00:00:00.000
52     1       1101    2009-05-14 00:00:00.000
76     1       1101    2009-02-04 00:00:00.000
78     2       1101    2009-09-29 00:00:00.000
79     1       1101    2009-09-25 00:00:00.000
79     2       1101    2009-09-25 00:00:00.000
80     1       1101    2009-02-09 00:00:00.000
80     1       1101    2012-09-26 00:00:00.000
80     2       1101    2009-02-09 00:00:00.000
80     2       1101    2012-09-26 00:00:00.000

I basically want to get only the rows that are like 79 or 80 where there is at least two rows that are the same in Col1 and Col3 but Col2 is different.

I'm new to SQL so I don't know if this is simple or not, I'm hoping somebody can help.

Thanks

I'm hoping to only display the rows that have the same values in column 1 and 3 but differ in column 2, so for the instance above only display the rows for 79 and 80.

Upvotes: 0

Views: 390

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

Simplest possible since you seem to only want the distinct Col1 values matching the criteria displayed should be;

SELECT DISTINCT Col1
FROM Table1
GROUP BY Col1,Col3
HAVING COUNT(DISTINCT Col2) > 1

An SQLfiddle to test with.

Upvotes: 1

podiluska
podiluska

Reputation: 51494

Using a combination of group by, having and count distinct should return what you want

select col1, col3, count (distinct col2)
from yourtable
group by col1, col3
having count(distinct col2) >= 2

If you want to display complete set of data, join this result set to the original data source.

select t.* from yourtable t
    inner join
(
    select col1, col3
    from yourtable
    group by col1, col3
    having count(distinct col2) >= 2
) v
    on t.col1 = v.col1
    and t.col3 = v.col3

Upvotes: 1

Related Questions