Question 280804 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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  |
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    You can do:

    select c1, c2, c3 from (
  select t.*, count(*) over(partition by c1, c2, c3) as c from t
) x where c = 1

    Result:

     c1  c2  c3 
 --- --- -- 
 c   r   w  
 c   s   d  
 c   w   w

    See running example at db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search