I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns.
Example: In the below example first two rows has same value for first 3 columns.But they have different value for column DEF. So both these rows to be listed in output.
But rows 2 and 4 has unique combination for first 3 columns but they have same values in DEF column.So not to be listed in output.
rows 5 and 6 are not to be listed since they are single row with different values.
+----------+-------+--------+--------+
| dept | role1 |role2 |DEF |
+----------+-------+--------+--------+
| a | abc | er | 0 |
| a | abc | er | 1 |
| b | qwer | ty | 0 |
| b | qwer | ty | 0 |
| c | der | ui | 1 |
| d | nerr | io | 0 |
+----------+-------+--------+--------+
output
+----------+------+------+------+
| dept | role1|role2 |DEF |
+----------+------+------+------+
| a | abc | er |0 |
| a | abc | er |1 |
+----------+------+------+------+
I tried using distinct with having but not able to check the values of column DEF to get desired result.
Can anyone help me on this?