Search code examples
sqlmysqlselectdistinct

How to Select only UNIQUE rows in SQL

I have a table as in sample below

   | C1  | C2  | C3 | C4 |
r1 | a   | b   | c  | 1  |
r2 | a   | b   | c  | 2  |
r3 | c   | s   | d  | 3  |
r4 | c   | w   | w  | 4  |
r5 | c   | r   | w  | 5  |

Here if we concatenate C1,C2 and C3 columns we see that r1 and r2 rows have same values (C4 is different). I want to Select such that r1 and r2 are dropped and only r3,r4 and r5 are selected.

Distinct cannot work since C4 is Unique and 'Group By' will retain one of the row.

I want an output as follows

   | C1  | C2  | C3 | C4 |
r3 | c   | s   | d  | 3  |
r4 | c   | w   | w  | 4  |
r5 | c   | r   | w  | 5  |
Solution

  • Just use group by and filter the groups by their count via having:

    select C1, C2, C3, min(C4) as C4
from yourtable
group by C1, C2, C3
having count(*) = 1