#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    Finding rows with duplicate data?


    Any help or direction would be appreciated not sure exactly where to start.

    I have one table 5 columns, ID is the primary key and the 4 additional columns are text fields.
    e.g.
    ID A B C D
    1 1 2 1 2
    2 1 2 1 2
    3 1 2 1 2
    4 1 2 1 2
    5 4 3 2 1
    6 4 3 2 1
    7 3 2 1 1

    When columns A, B, C, D all match for four or more rows (IDís) I would like to return those IDís

    In the above example I would like the query to return ID = 1,2,3,4. As those ID's all have A=1, B=2, C=1 and D-2.
    Note Columns A, B, C, D are text fields and there will be multiple IDs that need to be returned with different values in columns A, B, C, D, I dont want to search for A=x, B=y etc.

    Thank you in advance for your assistance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Code:
    select id 
      from t
     where (c1,c2,c3,c4) in
          (select c1,c2,c3,c4
             from t
            group
               by c1,c2,c3,c4
           having count(*) >= 4)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Got it thank you.

IMN logo majestic logo threadwatch logo seochat tools logo