>I have a list of people that I need to do a duplicate name check on and display the results along with the IDs. I was going to do a simple GROUP BY, but then I'd have to join back to the original table to get the ID. Is there something sexier? (11:30 on a Saturday night and I'm asking about sexy SQL queries....)

Hi, Mike,

A question - would the dup names have the same ID...or different IDs?

If the dup names would ALWAYS have the same ID, you could do this....

SELECT 	UserID, FirstName, LastName  
      FROM 	Users
       GROUP BY UserID, FirstName, LastName
             HAVING COUNT(*) > 1

However, if the dupes could have different IDs (for instance, Kevin Goff is in there with an ID of 1 and an ID of 2), then your code is correct. In that situation, when you have to 'dive back' into the data to get a column that wasn't part of the grouping, a subquery is necessary.

So if you have this (Kevin Goff is in there twice with the same ID, and Mike Cole is in there with different IDs)

insert into users  values (1, 'Kevin', 'Goff'),  (1, 'Kevin','Goff'), (2, 'John', 'Smith'), (3, 'Mike', 'Cole'), (4, 'Mike', 'Cole')

And you want to see both Kevin and Mike, but not John, then this is needed:

SELECT 	Users.UserID, UN.FirstName, UN.LastName FROM 	Users users
      INNER JOIN ( SELECT  FirstName, LastName
        FROM 	Users
           GROUP BY FirstName, LastName
	HAVING COUNT(*) > 1
                 ) un ON (un.FirstName = users.FirstName AND un.LastName = users.LastName)

No functional benefit, but here is where some people use common table expressions:

;WITH DupeListCTE as  (  SELECT 	FirstName, LastName
    FROM 	Users
      GROUP BY FirstName, LastName
            HAVING COUNT(*) > 1 )

SELECT 	Users.UserID, DupeListCTE.FirstName, DupeListCTE.LastName FROM Users users
    INNER JOIN DupeListCTE
        ON  (DupeListCTE.FirstName = users.FirstName AND DupeListCTE.LastName = users.LastName)