SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast Loco's Avatar
    Join Date
    Sep 2003
    Location
    England
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query Assistance: Selecting rows where 2 fields are unique

    Hey all, I'm wondering if someone here would mind assisting me with a little problem I've come up against regarding an SQL query.

    Here's the situation:

    We have an extensive database of user information (some 25,000 names) of which there are a number of duplicate entries submitted. What we need to do is select all rows where the First name, Last name, and/or email are unique.

    Some rows have the same email address, and last name, but a different first name (in the case of a family signing up with the same email address and surname).

    Distinct will not work (to my knowledge), since each entry also has a timestamp, therefore making every row unique.

    The goal here is to simply prune duplicate entries with an sql query - something I've not managed to figure out.

    If this makes sense, I'd appreciate your input on the subject, even if it's to tell me I'm fighting a losing battle since I can't even explain my problem properly.

    Thanks in advance!

    Shaun

    www.saveangel.org

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you've explained it fine

    which combination(s) of columns need to be unique?

    perhaps show a few sample rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast Loco's Avatar
    Join Date
    Sep 2003
    Location
    England
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,

    thanks for your speedy response.

    The database was set up for an online petition for our campaign.

    We've got the following fields:

    ID - title - first_name - last_name - dob - address - thoughts - reasons - email - website - timestamp

    there are a few other unimportant fields, extra address and some flags for various features...

    However, there are lot of entries which contain completely identical information (except for the timestamp field), which when we extract we would like to remove. However, naturally some people do have the same name, but a different email address. Others might have the same surname and email address, but a different first name. (in the case of families with one email account.)

    We're basically trying to pull out as tidy a result set as we can, containing as few duplicate entries as possible.

    I'm not as familiar as I used to be with this stuff, having not done so much web work recently as I used to.

    I hope I'm providing more clarity on the issue rather than making it more confusing!

    Thanks,

    Shaun

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, i am not prepared to make the decision for you about which combination(s) of columns need to be unique

    so i'll give you an example

    let's start with -- same email, same surname.more than one row

    Code:
    select ID
         , title
         , first_name
         , last_name
         , dob
         , address
         , thoughts
         , reasons
         , email
         , website
         , timestamp
      from yourtable zz
     where 1
         < ( select count(*)
               from yourtable
              where email = zz.email
                and last_name = zz.last_name
           )
    order
        by email
         , last_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast Loco's Avatar
    Join Date
    Sep 2003
    Location
    England
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Problem solved. Thanks a great deal for your help on this issue!


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
  •