CODEWITHSUNDEEP
sqlsql-server-2005
DenaliHardtail
DenaliHardtail

Reputation: 28336

How do I create a SQL Distinct query and add some additional fields

I have the following query that selects combinations of first and last names and show me dupes. It works, not problems here.

I want to include three other fields for reference; Id, cUser, and cDate. These additional fields, however, should not be used to determine duplicates as I'd likely not end up with any duplicates.

SELECT * FROM
(SELECT FirstName, LastName, COUNT(*) as "Count"
FROM Contacts
WHERE ContactTypeID = 1
GROUP BY LastName,FirstName
) AS X
WHERE COUNT > 1
ORDER BY COUNT DESC

Any suggestions? Thanks!

Upvotes: 2

Views: 143

Answers (2)

Unreason
Unreason

Reputation: 12704

If these fields are always the same then you can include them in GROUP BY and it will not affect the detection of duplicates

If they are not then you must decide what kind of aggregate function you will apply on them, for example MAX() or MIN() would work and would give you some indication of which values are associated with some of the attributes for the duplicates.

Otherwise, if you want to see all of the records you can join back to the source

SELECT X2.* FROM
(SELECT FirstName, LastName, COUNT(*) as "Count"
FROM Contacts
WHERE ContactTypeID = 1
GROUP BY LastName,FirstName
) AS X INNER JOIN Contact X2 ON X.LastName = X2.LastName AND X.FirstName = X2.FirstName 
WHERE COUNT > 1
ORDER BY COUNT DESC

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425833

SELECT  *
FROM    (
        SELECT  *, COUNT(*) OVER (PARTITION BY FirstName, LastName) AS cnt
        FROM    Contacts
        WHERE   ContactTypeId = 1
        ) q
WHERE   cnt > 1
ORDER BY
        cnt DESC

This will return all fields for each of the duplicated records.

Upvotes: 2

Related Questions