Below is an example of a SQL query which you can use in a scenario where you want to display a list of duplicate records which are stored in a particular table.
Below is the query that has been used to do the needful on the Leads entity (where Client wanted to have the list of Duplicate Lead records on the basis of Person First name, Last name, Address, City and State).
SELECT COUNT(*) AS Amount,
Lead_PersonFirstName, Lead_PersonLastName,
Lead_CompanyAddress1,Lead_CompanyAddress2,Lead_CompanyCity,
Lead_CompanyState
INTO ##Temp
FROM Lead
where lead_deleted is null
GROUP BY
Lead_PersonFirstName, Lead_PersonLastName,
Lead_CompanyAddress1,Lead_CompanyAddress2,Lead_CompanyCity,
Lead_CompanyState
HAVING Count(*) > 0
SELECT
A.Lead_PersonFirstName, A.Lead_PersonLastName,
A.Lead_CompanyAddress1, A.Lead_CompanyAddress2, A.Lead_CompanyAddress3,
A.Lead_CompanyAddress4, A.Lead_CompanyCity, A.Lead_CompanyState
FROM Lead A, ##Temp B
WHERE A.Lead_PersonFirstName = B.Lead_PersonFirstName AND
A.Lead_PersonLastName = B.Lead_PersonLastName
And lead_deleted is null And
A.Lead_CompanyAddress1 = B.Lead_CompanyAddress1 AND
A.Lead_CompanyAddress2 = B.Lead_CompanyAddress2 AND
A.Lead_CompanyCity = B.Lead_CompanyCity AND
A.Lead_CompanyState = B.Lead_CompanyState
Order By A.Lead_PersonFirstName , A.Lead_PersonLastName
Hope this helps.