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

Solution 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.

Solution 2

One way to do this is to use having and group by

esben=# select * from test;
 id | a | b | c | d
----+---+---+---+---
  1 | 1 | 2 | 3 | 4
  2 | 1 | 2 | 3 | 4
  3 | 1 | 1 | 3 | 2
  4 | 1 | 1 | 3 | 2
(4 rows)

esben=# select count(id),a,b,c,d from test group by a,b,c,d having count(id) >1;
 count | a | b | c | d
-------+---+---+---+---
     2 | 1 | 2 | 3 | 4
     2 | 1 | 1 | 3 | 2
(2 rows)

This doesn't list the actual id's though, but without the actual output you want it is hard to tell you how to get about that.

Solution 3

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