Finding wrong duplicate data in sql

Question

Hi I recently started working as a software engineer so this question might be too noob but please bear with me.

I have three columns, let's say fruit, color, taste.

Fruit can have many colors and tastes. But one fruit which has a specific color should have a same taste. For example, (apple, red) should have only one taste. (apple, red, sour) and (apple, red, sweet) can't exist together.

The problem I have is my table contains multiple duplicate tastes with same fruit and color. I have tried different joins but got wrong results.

Sorry for such an awful description but if anyone could understand and help me I would really appreciate.

Could you please post an example of your tables and the queries you have tried? — slasky, May 01 '18 at 19:27
HI and welcome to SO. The answer you seek can be found here. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 01 '18 at 19:27
Example question: https://stackoverflow.com/q/50008226/6327676 — DxTx, May 01 '18 at 19:29
"I have tried different joins".. why would you try joins if you only have one table with three columns? Something tells me you are leaving important details out of your question. — Tab Alleman, May 01 '18 at 19:36

score 0 · Answer 1 · answered May 01 '18 at 19:28

Use row_number() function

select * from (select *,
                    row_number() over (partition by fruit, taste order by fruit) Seq 
               from table t) t 
where Seq > 1;

By this you found Seq nos > 1 if fruit has duplicate taste and you can filter them & remove by using subquery with delete statment

delete t 
from  (select *,
           row_number() over (partition by fruit, taste order by fruit) Seq 
       from table t) t
where Seq > 1;

Andomar · Accepted Answer · 2018-05-01T19:42:43.943

0

To find fruits that have multiple tastes for the same color:

select  fruit
from    fruit
group by
        fruit
,       color
having  count(distinct taste) > 1

To list all rows related to these fruits:

select  *
from    (
        select  count(*) over (partition by fruit, color) cnt
        ,       *
        from    fruits
        group by
                fruit
        ,       color
        ,       taste
        ) sub
where   cnt > 1

Working example at SQL Fiddle.

edited May 01 '18 at 19:42

answered May 01 '18 at 19:30

Andomar

232,371
49
380
404

Group by is not necessary on the second and I already posted the correct syntax – paparazzo May 01 '18 at 19:45
@paparazzo: the `group by` helps when there are multiple rows with the same fruit, color and taste. Try it at the linked SQL Fiddle – Andomar May 01 '18 at 19:46
I suspect if there are multiple OP would want to see then. – paparazzo May 01 '18 at 19:47

paparazzo · Answer 3 · 2018-05-01T19:46:05.483

0

Put a PK or unique constraint on fruit, color

select * 
from (select *, count(*) over (partition by fruit, color) as cnt 
      from table 
     ) t 
where t.cnt > 1 
order by fruit, color

edited May 01 '18 at 19:46

answered May 01 '18 at 19:31

paparazzo

44,497
23
105
176

Finding wrong duplicate data in sql

3 Answers3