Select all columns with one column having a different value.

Question

In my table, some records have all column values ​​except one. I need to write a request to get these records. what's the best way to do this? the table is as follows:

colA colB colC abc abd abe 

What is the best way to get all records with all columns? Thanks for helping everyone.

Answer 1

Assuming you know that column3 will always be different to get 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, you can join it 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 

Answer 2

Just select those lines that have different meanings:

 SELECT col1, col2 FROM myTable WHERE colWanted != knownValue 

If this is not what you are looking for, send sample data in the table and the required output.

Answer 3

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.

Answer 4

Assuming col3 has difs

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

OR SHOW ALL 3 COLS

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