SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot imagize's Avatar
    Join Date
    Oct 2004
    Location
    Australia
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query to delete non unique values

    Is there a mysql query that deletes all non-unique rows from a table? i.e delete

    Table: Name

    Greg
    Barry
    Tom
    Greg
    Jason
    Greg

    becomes


    Greg
    Barry
    Tom
    Jason

  2. #2
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    search for my threads in the SQL forum. I had a thread once where I wanted to delete duplicates from a table. You'll find your answer there.


    -asT.

  3. #3
    Non-Member bronze trophy geniusgoalie's Avatar
    Join Date
    Sep 2004
    Location
    Buffalo, USA
    Posts
    979
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT distinct first_name FROM mytable

  4. #4
    SitePoint Zealot imagize's Avatar
    Join Date
    Oct 2004
    Location
    Australia
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about if i have 3 fields, I want 2 of them to be unique but the 3rd not?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    three fields, two of them unique:

    select field1, field2, min(field3) as minfield3
    from yourtable
    group by field1, field2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot imagize's Avatar
    Join Date
    Oct 2004
    Location
    Australia
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically I have this table structure

    ip_address - file - file_size

    I need a query to select all the rows in this table but DONT select any rows where the ip_address and file are the same.

    eg:

    234.342.2.2 - download/file.zip - 923742342
    234.342.2.2 - download/file.zip - 923742342
    234.342.2.2 - download/file2.zip - 345222

    In this example only

    234.342.2.2 - download/file.zip - 923742342
    234.342.2.2 - download/file2.zip - 345222

    should be selected as one of thosw rows appears more than twice.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    select ip_address, file, min(file_size) as min_file_size
    from yourtable
    group by ip_address, file
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot imagize's Avatar
    Join Date
    Oct 2004
    Location
    Australia
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That will select the unique values, i want to delete the non unique values.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, but that's what you asked for, to select the unique values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •