How do I find duplicates across multiple columns

Question

So I want to do something like this sql code below   select s id  s name s city  from stuff s group by s name having count where city and name are identical   gt  1   To produce the following   but ignore where only name or only city match  it has to be on  both columns    id      name  city    904834  jim   London   904835  jim   London   90145   Fred  Paris    90132   Fred  Paris 90133   Fred  Paris

User · Answer

You have to self join stuff and match name and city  Then group by count   select     s id  s name  s city  from stuff s join stuff p ON      s name   p city OR s city   p name   group by s name having count s name   gt  1

User · Answer

Using count    over partition by     provides a simple and efficient means to locate unwanted repetition  whilst also list all affected rows and all wanted columns  SELECT     t   FROM       SELECT         s           COUNT    OVER  PARTITION BY s name  s city  AS qty     FROM stuff s       t WHERE t qty  gt  1 ORDER BY t name  t city  While most recent RDBMS versions support count    over partition by     MySQL V 8 0 introduced  quot window functions quot   as seen below  in MySQL 8 0    CREATE TABLE stuff     id   INTEGER  NOT NULL    name VARCHAR 60  NOT NULL    city VARCHAR 60  NOT NULL        INSERT INTO stuff id name city  VALUES     904834  jim   London      904835  jim   London      90145  Fred   Paris      90132  Fred   Paris      90133  Fred   Paris       923457  Barney   New York     not expected in result       SELECT     t   FROM       SELECT         s           COUNT    OVER  PARTITION BY s name  s city  AS qty     FROM stuff s       t WHERE t qty  gt  1 ORDER BY t name  t city       id   name   city     qty -----     ---    -----   --   90145   Fred   Paris      3  90132   Fred   Paris      3  90133   Fred   Paris      3 904834   jim    London     2 904835   jim    London     2   db lt  gt fiddle here  Window functions   MySQL now supports window functions that  for each row from a query  perform a calculation using rows related to that row  These include functions such as RANK    LAG    and NTILE    In addition  several existing aggregate functions now can be used as window functions  for example  SUM   and AVG    For more information  see Section 12 21     Window Functions

User · Answer

Given a staging table with 70 columns and only 4 representing duplicates  this code will return the offending columns   SELECT      COUNT         LTRIM RTRIM S TransactionDate         LTRIM RTRIM S TransactionTime        LTRIM RTRIM S TransactionTicketNumber         LTRIM RTRIM GrossCost    FROM Staging dbo Stage S GROUP BY      LTRIM RTRIM S TransactionDate         LTRIM RTRIM S TransactionTime        LTRIM RTRIM S TransactionTicketNumber         LTRIM RTRIM GrossCost    HAVING COUNT     gt  1

User · Answer

Something like this will do the trick  Don t know about performance  so do make some tests   select   id  name  city from    stuff  s where 1  lt   select count    from  stuff  i where i city   s city and i name   s name

User · Answer

SELECT name  city  count    as qty   FROM stuff   GROUP BY name  city HAVING count    gt  1

User · Answer

A little late to the game on this post  but I found this way to be pretty flexible   efficient   select      s1 id      s1 name      s1 city  from      stuff s1      stuff s2 Where     s1 id  lt  gt  s2 id     and s1 name   s2 name     and s1 city   s2 city

User · Answer

Duplicated id for pairs name and city   select s id  t    from  stuff  s join       select name  city  count    as qty     from  stuff      group by name  city     having count     gt  1   t on s name   t name and s city   t city

[sql] How do I find duplicates across multiple columns?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to duplicates