Search code examples
sqlselectdistinctmultiple-records

Get multiple records only using SELECT DISTINCT or similar

I have records like this:

Column1 Column2
A       Blue
A       Blue
B       Red
B       Green
C       Blue
C       Red

Using SELECT DISTINCT I get this:

Column1 Column2
A       Blue
B       Red
B       Green
C       Blue
C       Red

What I'd like to get:

Column1 Column2
B       Red
B       Green
C       Blue
C       Red

So I need to get only multiple records of column1 that have different values on column2. (I'm joining two tables)

With SELECT DISTINCT, I got closer to what I need, but I can't find a way to exclude records like "A" on column1 that have always the same value on column2...

Solution

  • Try this:

    SELECT * FROM yourtable
WHERE Column1 IN
(SELECT Column1
 FROM yourtable
 GROUP BY Column1
 HAVING COUNT(DISTINCT Column2) > 1
)

    The DISTINCT in COUNT ensures that you only get those records where Column2 has multiple distinct values.