CODEWITHSUNDEEP
sqlsql-server
jeri rayn
jeri rayn

Reputation: 105

select all columns with one column has different value

In my table,some records have all column values are the same, except one. I need write a query to get those records. what's the best way to do it? the table is like this:

 colA  colB colC
   a     b    c
   a     b    d
   a     b    e

What's the best way to get all records with all the columns? Thanks for everyone's help.

Upvotes: 7

Views: 6927

Answers (4)

user1166147
user1166147

Reputation: 1610

Assuming col3 has the difs

SELECT Col1, Col2
FROM Table
GROUP BY Col1, Col2
HAVING COUNT(*) > 1

OR TO SHOW ALL 3 COLS

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming you know that column3 will always be different, to get the rows that have more than one value:

SELECT Col1, Col2
FROM Table t
GROUP BY Col1, Col2
HAVING COUNT(distinct col3) > 1

If you need all the values in the three columns, then you can join this back to the original table:

SELECT t.*
FROM table t join
     (SELECT Col1, Col2
      FROM Table t
      GROUP BY Col1, Col2
      HAVING COUNT(distinct col3) > 1
     ) cols
     on t.col1 = cols.col1 and t.col2 = cols.col2

Upvotes: 8

Leasure
Leasure

Reputation: 31

How about something like

SELECT Col1, Col2
FROM Table
GROUP BY Col1, Col2
HAVING COUNT(*) = 1

This will give you Col1, Col2 that have unique data.

Upvotes: 1

Oded
Oded

Reputation: 499002

Just select those rows that have the different values:

SELECT col1, col2
FROM myTable
WHERE colWanted != knownValue

If this is not what you are looking for, please post examples of the data in the table and the wanted output.

Upvotes: 1

Related Questions