Select and display duplicates in a table

_CODER_
_CODER_ used Ask the Experts™
on
I need to write a select statement that will show all records that have the same "UserCode" in a table.
I have the below but it's only showing one of each duplicate. I need to list them out so it will show each user...
What would I write to show each duplicate so I can compare them.

SELECT UserCode, FirstName, LastName
FROM Users
WHERE UserCode is not null
GROUP BY UserCode
HAVING (COUNT(UserCode) > 1 )

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
try this
SELECT U.*
FROM Users U
	INNER JOIN ( 
		SELECT UserCode
		FROM Users
		WHERE UserCode is not null
		GROUP BY UserCode
		HAVING (COUNT(UserCode) > 1 ) D
	ON U.UserCode = D.UserCode
ORDER BY U.UserCode	

Open in new window

Commented:

SELECT 
Users.UserCode, 
Users.FirstName, 
Users.LastName

FROM Users

WHERE Users.UserCode is not null
AND (SELECT COUNT(*) FROM Users X WHERE X.UserCode = Users.UserCode) > 1
ORDER BY Users.UserCode;

Open in new window

HainKurtSr. System Analyst
Commented:
or

select UserCode, FirstName, LastName from users where UserCode in (
SELECT UserCode
FROM Users
WHERE UserCode is not null
GROUP BY UserCode
HAVING (COUNT(UserCode) > 1 )
) order by UserCode

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial