sql query distinct on multiple columns

Question

i have this data and i am trying to find cases where there are different ids but duplicate data in Field 1,2,3,4

id    field1 field2 field3 field4    
====  ====== ====== ===== =======    
1       A      B     C      D    
2       A      B     C      D    
3       A      A     C      B   
4       A      A     C      B

so, in whatever way possible, in this case i want it to somehow show me:

1 & 2 are duplicates 3 & 4 are duplicates

Answer 1

Instead of SELECT DISTINCT, select the fields and a count of rows. Use HAVING to filter out items with more than one row, e.g:

select field1
      ,field2
      ,field3
      ,field4
      ,count (*)
  from foo
 group by field1
         ,field2
         ,field3
         ,field4
having count (*) > 1

You can then join your original table back against the results of the query.

Answer 2

SELECT * FROM [TableName] WHERE ID IN( SELECT MIN(ID) FROM [TableName] GROUP BY CONCAT(field1, field2, field3, field4))

This will return the full row for id's 1 & 3