Finding duplicates based on multiple columns

Question

I need to find all rows which are duplicates across different locations. Table data is as below,

---------------------------------------------------------
| Assetno  |  SerialNo  |   StickerNo  |     location  |
---------------------------------------------------------
| 1234     |   QWR      |   12ERT      |     123       |
| 1234     |   QWR      |   12ERT      |     567       |
| 7888     |   ytu      |   67UI       |     456       |
| 9000     |   UIO      |   OPIO9      |     8         |
---------------------------------------------------------

Like in the above table I need to find rows like row number 1 and 2.

ROW_NUMBER() OVER(PARTITION BY <> ORDER BY <>? What did you try, show us your attempt and the desired output — Ilyes
– Ilyes, Commented Jun 10, 2019 at 21:25
Possible duplicate of How do I find duplicates across multiple columns? — M. Kanarkowski
– M. Kanarkowski, Commented Jun 10, 2019 at 21:30

Gordon Linoff · Accepted Answer · 2019-06-10 22:02:36Z

3

One method uses window functions. So, this works on your sample data:

select t.*
from (select t.*,
             count(*) over (partition by Assetno, SerialNo, StickerNo) as cnt
      from t
     ) t
where cnt >= 2;

I would be more inclined to use exists, though:

select t.*
from t
where exists (select 1
              from t t2
              where t2.Assetno = t.Assetno and
                    t2.SerialNo = t.SerialNo and
                    t2.StickerNo = t.StickerNo and
                    t2.location <> t.location
             );

This more explicitly specifies that the rows with the first three columns the same have different locations. It is probably faster with an index on (Assetno, SerialNo, StickerNo, location) as well.

edited Jun 10, 2019 at 22:02

answered Jun 10, 2019 at 21:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Linoff Over a year ago

@RaymondNijland . . . Not at all. The intention is to pick up all duplicate rows, as requested by the OP.

Raymond Nijland Over a year ago

"Not at all. The intention is to pick up all duplicate rows, as requested by the OP. " indeed your right i missed that point.. Adding the ORDER BY as in count(*) over (partition by Assetno, SerialNo, StickerNo ORDER BY <column>) as cnt in this case will add unneeded processing

Abhi Over a year ago

Thank You so much, both the methods worked like a champ.

Collectives™ on Stack Overflow

Finding duplicates based on multiple columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related