Query to check duplicate field values

Sometimes you need to check if we got duplicate values for a given field. This is a script, we can use to check them.

Suppose I have new_RegNumber field in Account Entity which needs to be unique. So below query will show me if there are any ducplicates by mistake.

SELECT A1.new_RegNumber
FROM Account as A1
WHERE A1.new_RegNumber is not null
GROUP BY A1.new_RegNumber
HAVING (SELECT COUNT(A2.new_RegNumber) FROM  Account A2 WHERE A2.new_RegNumber = A1.new_RegNumber)>1

Hope this is useful.