Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2010
    Posts
    13

    Unanswered: removing duplicate ids

    Hey all,

    Is there a simple query to remove duplicate ids - where you just check if the id has duplicates and nothing else? Some of the records have four or five duplicate ids.

    I tried this:
    Code:
    SELECT KeepThese.* INTO ready_for_print
    FROM KeepThese
    WHERE id IN (SELECT id
                       FROM KeepThese
                       GROUP BY fullname, addresses
                       HAVING COUNT(*) < 2);
    But this gives me error:
    "You tried to execute a query that does not include the specified expression 'id' as part of an aggregate function.
    Last edited by johnmerlino; 10-20-10 at 23:46.

  2. #2
    Join Date
    May 2010
    Posts
    601
    See: Subquery Basics
    Scroll down to Delete duplicate records
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Oct 2010
    Posts
    13
    Your example seems to get rid of records that had duplicate names. I want to preserve the records that have duplicate ids, but only one instance of them.

  4. #4
    Join Date
    Oct 2010
    Posts
    13
    I try this:
    Code:
    SELECT KeepThese.* INTO ready_for_print
    FROM KeepThese
    WHERE ID <>  (SELECT Min(ID) AS MinOfID FROM KeepThese AS Dupe      
    WHERE (Dupe.id  = KeepThese.id));
    And it appends nothing in new table.

  5. #5
    Join Date
    Oct 2010
    Posts
    13
    I have this:
    333 Marge, John State Road
    333 Marge, John State Road
    444 Marge, John Cap Road
    444 Marge, John Cap Road
    515 Marge, John 3rd Street


    I would want to keep only the following:
    333 Marge, John State Road
    444 Marge, John Cap Road
    515 Marge, John 3rd Street

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by johnmerlino View Post
    I try this:
    Code:
    SELECT KeepThese.* INTO ready_for_print
    FROM KeepThese
    WHERE ID <>  (SELECT Min(ID) AS MinOfID FROM KeepThese AS Dupe      
    WHERE (Dupe.id  = KeepThese.id));
    And it appends nothing in new table.
    That would be correct. Your SQL is a select statement. It is not an append query (insert).
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by johnmerlino View Post
    Your example seems to get rid of records that had duplicate names. I want to preserve the records that have duplicate ids, but only one instance of them.
    You did ask originally:

    Is there a simple query to remove duplicate ids ...
    You never said previously that you want to preserve the records.

    I would just add a field in the table for "duplicate" and use an update query to mark then as dups.

    I would avoid moving data between two identical tables. Lots of room for issues.

    This way you have the dups preserved.

    You would use this new "duplicate" field to filter out the records. This will have the appearance as if you have two different table when in reality you do NOT have two tables.
    Last edited by HiTechCoach; 10-21-10 at 16:55.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    May 2010
    Posts
    601
    I see you posted this also HERE

    See: A message to forum cross posters
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by HiTechCoach View Post
    which part of this were you referring to?
    Page not found

    user warning: Incorrect key file for table 'node'; try to repair it query: SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = 7 in /home/excel13/public_html/includes/database.mysql.inc on line 121.

    user warning: Incorrect key file for table 'node'; try to repair it query: SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = 7 in /home/excel13/public_html/includes/database.mysql.inc on line 121.

    user warning: Incorrect key file for table 'node'; try to repair it query: SELECT nid FROM node WHERE nid = 7 in /home/excel13/public_html/includes/database.mysql.inc on line 121.

    user warning: Incorrect key file for table 'node'; try to repair it query: SELECT n.nid, n.title, u.uid, u.name FROM node n INNER JOIN node_counter s ON n.nid = s.nid INNER JOIN users u ON n.uid = u.uid WHERE s.daycount <> '0' AND n.status = 1 ORDER BY s.daycount DESC LIMIT 0, 5 in /home/excel13/public_html/includes/database.mysql.inc on line 121.

    user warning: Incorrect key file for table 'node'; try to repair it query: SELECT n.nid, n.title, u.uid, u.name FROM node n INNER JOIN node_counter s ON n.nid = s.nid INNER JOIN users u ON n.uid = u.uid WHERE s.totalcount <> '0' AND n.status = 1 ORDER BY s.totalcount DESC LIMIT 0, 10 in /home/excel13/public_html/includes/database.mysql.inc on line 121.

    user warning: Incorrect key file for table 'node'; try to repair it query: SELECT n.nid, n.title, u.uid, u.name FROM node n INNER JOIN node_counter s ON n.nid = s.nid INNER JOIN users u ON n.uid = u.uid WHERE s.timestamp <> '0' AND n.status = 1 ORDER BY s.timestamp DESC LIMIT 0, 5 in /home/excel13/public_html/includes/database.mysql.inc on line 121.



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2010
    Posts
    601
    Oh ... looks like the that web site is having database issues.

    I will see if I can contact them.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Tags for this Thread

Posting Permissions

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