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

    Join Date
    Nov 2003
    Posts
    53
    Rep Power
    11

    Finding records that vary only by one field


    Hi,
    I want to isolate records in a large dataset where there is a difference on only one field.

    For example, if my headers are:
    aspect1 aspect2 aspect3 aspect4

    I want to find all records where aspect1, aspect2 and aspect3 are the same, but aspect4 is different.

    Is there a way to do that?

    Thank you for any insight,
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by One Stupid Guy
    I want to find all records where aspect1, aspect2 and aspect3 are the same, but aspect4 is different.
    Code:
    SELECT t.aspect1
         , t.aspect2
         , t.aspect3
         , t.aspect4
      FROM ( SELECT aspect1
                  , aspect2
                  , aspect3
               FROM daTable
             GROUP
                 BY aspect1
                  , aspect2
                  , aspect3
             HAVING COUNT(*) > 1 ) AS d
    INNER
      JOIN daTable AS t
        ON t.aspect1 = d.aspect1
       AND t.aspect2 = d.aspect2
       AND t.aspect3 = d.aspect3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    53
    Rep Power
    11
    Thank you r937,
    You're considerably better than Mayor Ford.

    I didn't get what I wanted though.

    I was able to determine that I really wanted instances where aspect1 and aspect2 were the same while aspect4 differed (ie, we can ignore aspect3 from my original post).

    Below is the code I used exactly. But it returned all records, so I missed something.

    GO
    SELECT t.[aspect1]
    ,t.[aspect2]
    ,t.[aspect4]

    FROM (SELECT [aspect1]
    ,[aspect2]

    FROM [daTable]

    GROUP BY [aspect1]
    ,[aspect2]

    HAVING COUNT(*) > 1) AS d

    INNER JOIN [daTable] AS t

    ON t.[aspect1] = d.[aspect1]
    AND t.[aspect2] = d.aspect2]

    ORDER BY [aspect1]
    ,[aspect2]
    ,[aspect4]
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    the only reason that query would return all rows in the table is because every single combination of aspect1 and aspect2 is repeated at least once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo