SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    New Zealand
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding duplicate entries

    To make it easier to explain my question, here is a sample record set:

    pid uid
    200 1
    201 1
    202 1
    100 2
    101 2
    102 2
    100 2
    101 2
    102 2

    I want to query a table like this (but with many more records) to find which uids have more than one entry for the same pid, e.g. for the example above, the result would be "2". I don't need to know how many duplicates there are, nor what the duplicate pids are. I just need to know which uids have duplicate pids.

    Also, unlike the example, the pids are not necessarily sequential.

    Is this possible?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by KiwiJohn View Post
    I just need to know which uids have duplicate pids.
    Code:
    SELECT uid
         , pid
      FROM daTable
    GROUP
        BY uid
         , pid
    HAVING COUNT(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    New Zealand
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome! Thanks Rudy, it works perfectly!


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
  •