Select statement to find duplicates on certain fields?

Question

Can you help me with SQL statements to find duplicates on multiple fields? For example, in pseudo code: select count(field1,field2,field3) from table where the combination of field1, field2, field3 occurs multiple times and from the above statement *if there are multiple occurrences* I would like to select *every record except the first one*.

Answer 1

Here's a solution to the problem:

SELECT

field1,

field2,

field3,

COUNT(*) AS cnt

FROM table

GROUP BY 1,2,3

HAVING cnt > 1

The second part of your question is a bit trickier, is there a way to determine the "first one" of duplicates?

Let's say the minimum value from field4 will get us the "first occurrence":

SELECT

t1.*

FROM

table t1

JOIN

(

SELECT

field1,

field2,

field3,

MIN(field4) AS field4,

COUNT(*) AS cnt

FROM table

GROUP BY 1,2,3

HAVING cnt > 1

) t2

ON t1.field1 = t2.field1 AND

t1.field2 = t2.field2 AND

t1.field3 = t2.field3 AND

t1.field4 = t2.field4