Results 1 to 13 of 13
  1. #1
    Join Date
    May 2007
    Posts
    7

    Unanswered: Duplicate Records in Multiple Columns.

    Hello,

    I have a question regarding duplicate records, the thing is I'm able to query for duplicated records if I type the following:

    Code:
    select ColumnName from TableName 
    where ColumnName in
    (
    select ColumnName from TableName 
    group by ColumnName having count(*) > 1
    )
    That gives me duplicate records for one column, but I need find duplicate records in more than one column (4 columns to be exact), but the way I need to find these records is they all have to be duplicate, what I'm trying to say is I don't don't want to find the following:

    First Last Age Email
    John Smith 25 jsmith@hotmail.com
    John Smith 26 jsmith@hotmail.com
    John Smith 25 jsmith4@hotmail.com

    I need to find the following:

    First Last Age Email
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com

    So all the columns must be exactly the same, that's the only condition I want to show the records, is there any way to do this?

    For the record, I'm using MS SQL Server 2000, thank you.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT ColumnName1
         , ColumnName2
         , ColumnName3
         , ColumnName4
    FROM   TableName 
    GROUP
        BY ColumnName1
         , ColumnName2
         , ColumnName3
         , ColumnName4
    HAVING Count(*) > 1
    EDIT: copy-pasted "ColumnName2" without changing the number oops!
    Last edited by gvee; 05-21-08 at 13:10.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, just an observation, but that query does not show the rows containing the dupes
    Last edited by r937; 05-21-08 at 13:59. Reason: corrected typo: it's george, nor geroge
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Based on the data given (the table has 4 columns), it does show what is in them (because they are the same) - it just doesn't show you how many results there are; which can be easily obtained by adding a Count(*) to the output.

    Based on the data given I would have said the answer was quite correct
    George
    Home | Blog

  5. #5
    Join Date
    May 2007
    Posts
    7
    Quote Originally Posted by georgev
    Code:
    SELECT ColumnName1
         , ColumnName2
         , ColumnName2
         , ColumnName2
    FROM   TableName 
    GROUP
        BY ColumnName1
         , ColumnName2
         , ColumnName2
         , ColumnName2
    HAVING Count(*) > 1
    This one gives me an error, but I have tried a similar method, the only problem is it returns duplicated records based on 1 column for each rather than based on all 4 columns, so:

    First Last Age Email
    John Smith 25 jsmith@hotmail.com
    John Smith 26 jsmith@hotmail.com
    John Smith 25 jsmith4@hotmail.com

    Not

    First Last Age Email
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by birko19
    I need to find the following:

    First Last Age Email
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com

    ...

    First Last Age Email
    John Smith 25 jsmith@hotmail.com
    John Smith 26 jsmith@hotmail.com
    John Smith 25 jsmith4@hotmail.com

    Not

    First Last Age Email
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com
    John Smith 25 jsmith@hotmail.com
    The goalposts have moved once again...
    George
    Home | Blog

  7. #7
    Join Date
    May 2007
    Posts
    7
    georgev, your query worked but I believe it gives the same result I had in the past, the following image should help clear my question I guess:

    http://img519.imageshack.us/my.php?i...plicatemw6.jpg

    The records with the red around them are the only ones I'm interested in seeing since both columns are duplicated exactly the same, the only reason why all the other records are showing because somewhere in there the First Names also have duplicated records somewhere else in the table, basically:

    FirstName LastName
    John Smith
    John Smith
    John Smith

    Is what I'm looking for, not:

    FirstName LastName
    Paul Smith
    Paul Smithers
    John Smith
    John Burgendy

    I hope I'm not being confusing

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Based on the data given (the table has 4 columns)...
    where does it say that the table has only those 4 columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    where does it say that the table has only those 4 columns?
    Quote Originally Posted by georgev
    Based on the data given I would have said the answer was quite correct
    To the OP: Have a look at the query I posted; if you drop column 3 and 4 from both the SELECT and GROUP BY statements you should be set

    EDIT: wait, you want all 4 columns on the output... gimme 2 secs
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT a.*
    FROM   tablename As [a]
     INNER
      JOIN 
      (
      SELECT column1
           , column2
      FROM   table1 
      GROUP
          BY column1
           , column2
      HAVING Count(*) >1
      ) As [b]
        ON a.column1 = b.column1
       AND a.column2 = b.column2
    Can't test this on this laptop but it looks about right in my head
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I like
    Code:
    SELECT *
       FROM TableName AS a
       WHERE 1 < (SELECT Count(*)
          FROM TableName AS b
          WHERE  b.Column1 = a.Column1
             AND b.Column2 = a.Column2
             AND b.Column3 = a.Column3
             AND b.Column4 = a.Column4)
    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    speed fight! speed fight!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2007
    Posts
    7
    Quote Originally Posted by georgev
    Code:
    SELECT a.*
    FROM   tablename As [a]
     INNER
      JOIN 
      (
      SELECT column1
           , column2
      FROM   table1 
      GROUP
          BY column1
           , column2
      HAVING Count(*) >1
      ) As [b]
        ON a.column1 = b.column1
       AND a.column2 = b.column2
    Can't test this on this laptop but it looks about right in my head
    georgev, you're a genius my friend, this worked like a charm, thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •