Unusual Duplicate Rows problem.

[Mayoor]

I have a web page which sometime allows duplicate entries of user submissions. Rather than restrict this at the database level, the client has asked for a report to list the User Id's(Identity field) of all duplicate records.

Now here is the problem. I am using this SQL to grab my records.

SELECT UserId
FROM UserProfile
GROUP BY Login, Firstname, Lastname, Email, PrimarySiteId, PreferredName, ManagerID
HAVING (COUNT(*) > 1)

However because the field [USERID] is not in my grouping clause the statement fails.

I must list all the User ID's of duplicate records

Can anyone see a way around this?

 

[vongrunt]

To simplify query... is there any primary key in table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Mayoor]

Von....

Yes the UserID is a uniquely generated PK

 

[vongrunt]

Stupid me...

The most extensible way is to self-join on all grouped columns. Something like:

SELECT UP.anycolumnsyouwant
FROM UserProfile UP
INNER JOIN
(	SELECT Login, Firstname, Lastname, Email, PrimarySiteId, PreferredName, ManagerID
	FROM UserProfile
	GROUP BY Login, Firstname, Lastname, Email, PrimarySiteId, PreferredName, ManagerID
	HAVING (COUNT(*) > 1) 
) UP2
ON UP2.Login         = UP.Login 
	AND UP2.Firstname     = UP.Firstname 
	AND UP2.Lastname      = UP.Lastname 
	AND UP2.Email         = UP.Email 
	AND UP2.PrimarySiteId = UP.PrimarySiteId 
	AND UP2.PreferredName = UP.PreferredName
	AND UP2.ManagerID     = UP.ManagerID

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)