SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECTing records with duplicate field data

    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!
    Last edited by Metallisoft; Aug 7, 2002 at 14:14.

  2. #2
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT ip,COUNT(ip)
    FROM luser 
    GROUP BY ip
    HAVING COUNT(ip) > 1
    and do the same for the email address...
    ----Adopt-a-Sig----
    Your message here!

  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sql language reference? see my sig
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by Metallisoft; Aug 5, 2002 at 13:34.

  5. #5
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about?

    SELECT Luser_LuserName, Luser_LastIP FROM Luser GROUP BY Luser_LuserName, Luser_LastIP HAVING Count(*)>1 ORDER BY Luser_LastIP
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  6. #6
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by crowdozer
    sql language reference? see my sig
    We need to find a newer Access T-SQL reference!

    Goof
    Nathan Rutman
    A slightly offbeat creative.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2002
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Crowdozer, that doesn't return any rows because the LuserName throws it off. Works if you take that out.

    Like my field names?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •