SELECTing records with duplicate field data

[Metallisoft]

For example, I want to return all records from the luser table that have a duplicate logged IP. Or Duplicate EMail. Sort of a reverse-DISTINCT query. I looked around a few sites, but none offered any mention of this, nor do I know what the keyword would be anyway..MSDN doesn't have a SQL language reference that I could find, etc.

Is there a keyword for this? Or will I have to return all rows and loop through myself? ugh. That's a bit of a pain with 100k + lusers..though speed isn't important since it's admin-only...but still!

[randem]

SELECT ip,COUNT(ip)
FROM luser 
GROUP BY ip
HAVING COUNT(ip) > 1

and do the same for the email address...

[JimBolla]

sql language reference? see my sig

[Metallisoft]

Yea, HAVING..just looked at that, but I didn't think you could use Count() that way!

Awesome, thx

Final query:
SELECT Luser_LuserName,Luser_LastIP FROM Luser WHERE Luser_LastIP IN (SELECT Luser_LastIP FROM Luser GROUP BY Luser_LastIP HAVING Count(*)>1 )ORDER BY Luser_LastIP

Had to do 2-stage because I wanted Luser's account name too.

[JimBolla]

how about?

SELECT Luser_LuserName, Luser_LastIP FROM Luser GROUP BY Luser_LuserName, Luser_LastIP HAVING Count(*)>1 ORDER BY Luser_LastIP

[Goof]

Originally posted by crowdozer
sql language reference? see my sig

We need to find a newer Access T-SQL reference!

Goof

[Metallisoft]

Crowdozer, that doesn't return any rows because the LuserName throws it off. Works if you take that out.

Like my field names?