CODEWITHSUNDEEP
mysql
silver_river
silver_river

Reputation: 169

Find duplicate and their unique ID

This is the fruit_project table:

ID    FRUIT
1     Mango
2     Apple
3     Banana
4     Carrot
5     Apple
6     Banana
7     Apple
8     Banana
9     Mango
10    Apple

This what I tried.

select id, fruit, count(*) dup  from fruit_project  
group by fruit 
having dup>1

The result is 

ID  FRUIT    DUP 
1   Mango    2
2   Apple    4
3   Banana   3

If I use GROUP_CONCAT on ID, the result will be

ID               FRUIT    DUP 
1,9              Mango    2
2,5,7,10         Apple    4
3,6,8            Banana   3

Result I need is

ID    FRUIT
1     Mango
2     Apple
3     Banana
5     Apple
6     Banana
7     Apple
8     Banana
9     Mango
10    Apple

In the result I need, the non-duplicate (4 Carrot) is not there, and not using COUNT function. (majority in web suggest to use COUNT) Any other way to find duplicate without COUNT? Like conditional formatting in Excel

Upvotes: 0

Views: 34

Answers (1)

MrApnea
MrApnea

Reputation: 1946

Here is one way to do it:

select * 
from your_table t1
where FRUIT IN (select FRUIT from your_table WHERE ID != t1.ID);

Upvotes: 2

Related Questions