SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    distinct problem

    Ok.. I made a mess and need to clean it up. I forgot to put any checking in my database to see if anyone was in it before allowing them to put themselves in. So, I have multiple entries and I would like to select distinct by email. I would like to select all the columns where the email is distinct.. I know to do it by one colum ie.. select distinct email from nytimes;

    TABLE
    Code:
    +-----------+------------------------+------+-----+---------------------+----------------+
    | Field     | Type                   | Null | Key | Default             | Extra          |
    +-----------+------------------------+------+-----+---------------------+----------------+
    | id        | mediumint(10) unsigned |      | PRI | NULL                | auto_increment |
    | name      | varchar(100)           |      |     |                     |                |
    | email     | varchar(100)           |      |     |                     |                |
    | address   | varchar(100)           |      |     |                     |                |
    | city      | varchar(100)           |      |     |                     |                |
    | state     | varchar(100)           |      |     |                     |                |
    | zip       | varchar(100)           |      |     |                     |                |
    | info      | varchar(10)            |      |     |                     |                |
    | timestamp | datetime               |      |     | 0000-00-00 00:00:00 |                |
    +-----------+------------------------+------+-----+---------------------+----------------+

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i shall make a guess that what you really want is just those rows where the email is not distinct, rather than all thos rows where it is

    select * from yourtable
    where email in
    (select email from yourtable
    group by email having count(*) > 1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That didn't seem to do it...

    Code:
    mysql> select * from nytimes where email in (select email from nytimes group by email having count(*) > 1);
    
    ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select email from nytimes group by email having count(*) > 1)'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are using an outdated version of mysql

    4.1 has been in production since october

    i always assume people on older versions will actually mention that fact when they are asking for sql help

    run the query that's inside the parentheses by itself

    this will give you a list of emails that are duplicated

    copy and paste those emails inside the parentheses where the subquery was

    don't forget to enclose them in quotes and separate them by commas, i.e.

    select * from yourtable
    where email in
    ('tom@example.com','dick@example.com','harry@example.com')

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok... I'm thinking about dumping the table locally (which I have 4.1.7 installed).

    I think that your statment might not work exactly what I'm looking for

    I need a list of all entries. However, I need to make sure there are no duplicate entires, so anyone who entered more than once, their duplicate records are erased in the output. ie.. they could have entered more than once with a different email, but I'm not worried about that.

    There are about 6,000 entries of which 4,000 are unique. even if I need to run a statement to permanately erase all the duplicates, I would be happy

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy's statement will give you what you are asking for, it will give you a list of those emails that are duplicates.

    HOW do you decide which of two records that you will keep? Are you keeping the earlier one entered or later?

  7. #7
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rudy's code didnt work on mine too.

    but this works.

    select *
    From table
    Group by field
    Having Count(*) > 1;

    gives you the duplicated rows/

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    defiance, just be careful, yours won't work in any other database but mysql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    opps..
    didnt know that.

    well, thanks for the note though

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you use group by you must name all non-aggregate columns (using min or max is an aggregate for instance) in the group by statement or you will get error message outside mysql. mysql will run the query with unpredictable results according to their own manual.


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
  •