Select Distinct and show records where X column is not the same

I have a database with a structure of:

I am struggling to write a query to show me where ‘Supp’ is different. (Supp Should be the same for all records, So i need to identify records effected to correct) The table contains more than 55,000 lines

Just a list of Codes where Supp is not the same for each of the Location would be sufficient. Selecting distinct or group by but unsure how to query the Supp if different. For example with the above Data: 1478 and 5689 would be the effected lines.

Any help would be greatly appreciated.

Answer

In the simplest case, to get a list of Codes that have more than one Supp:

SELECT Code
  FROM testtable
  GROUP BY Code
  HAVING COUNT(DISTINCT Supp) >= 2

Output:

1478
5689

If you want to get the full rows (or partial by changing SELECT *), try something like:

SELECT a.*
  FROM testtable a
    INNER JOIN (
                SELECT Code
                  FROM testtable
                  GROUP BY Code
                  HAVING COUNT(DISTINCT Supp) >= 2
                ) b ON b.Code = a.Code

Output:

Location    Code    Supp    Ref
1           1478    CAM001  203442
2           1478    CAM001  203442
99          1478    COL001  203442
1           5689    SAM001  65789
2           5689    SUL001  65789
99          5689    SUL001  65789

See this in action on sqlfiddle.