Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    16

    Unanswered: Removing Duplicates

    Hi,

    I have a duplicates query, and I want to see only unique records with their most recent date.

    for example:

    LAW Name1 2/3/2004
    HR Name1 8/6/2004
    TIS Name1 7/31/2004
    CPM Name2 3/8/2003
    TIS Name2 12/30/2003

    Right now, I'm manually deleting the duplicate entries, but is there any way i can run a query to remove the dupes?

    In the above examples, for Name1 I want to retain the HR 8/6 record, and for Name2 I want to retain the TIS 12/30 record.

    Help!

  2. #2
    Join Date
    Jul 2004
    Posts
    16
    Quote Originally Posted by r937
    lastknightess, your question is really different from the other question in this thread, it should perhaps have been its own thread

    but here you go anyway:
    Code:
    select dept
         , name
         , foodate
      from yourtable as x
     where foodate
         = (select max(foodate)
              from yourtable
             where name = x.name )
    this query uses a correlated subquery to perform the necessary grouping
    hi, i'm afraid i need a little more help
    here is what the duplicate query looks like rite now, i'd appreciate it if you could show me how it should be modified. Thank you much!

    SELECT AMO.NAME, AMO.AREA, AMO.LCE
    FROM AMO
    WHERE (((AMO.NAME) In (SELECT [NAME] FROM [AMO] As Tmp GROUP BY [NAME] HAVING Count(*)>1 )))
    ORDER BY AMO.NAME;

  3. #3
    Join Date
    Dec 2003
    Posts
    268

    Try this.

    select name, area, lce
    from amo as X
    where name = (select name
    from amo
    group by name
    having count(x.name)>1)

    I think this should return the values you want.

  4. #4
    Join Date
    Jul 2004
    Posts
    16
    ^ i get an error message: "at most one record can be returned from this subquery" and then it does nothing.

    any idea why?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How does it come to pass that you have unnecessary records in your database? It is very, VERY rare that a well designed database would need to have this sort of action taken with any sort of frequency. I question your schema...

    That said:

    Think in reverse. Don't delete records from your current table, create a table with what you need.

    SELECT DISTINCT whatever INTO newTable
    FROM yourTable

    Then drop yourTable and replace it with newTable.

    That's the basic gyst, actual query and steps will vary according to exactly what you need done and when.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are no unnecessary rows in lastknightess' table

    have a look at the data and you'll see

    you have to interpret what "remove dupes" really means

    she didn't mean completely duplicate rows (as would be the normal interpretation in an sql query context)

    she meant "see only unique records with their most recent date"

    more specifically, the rows having the groupwise maximum of a certain column

    here's the desired query:
    Code:
    SELECT NAME
         , AREA
         , LCE
      FROM AMO as x
     WHERE LCE
         = (select max(LCE)
              from AMO
             where NAME = x.NAME ) 
    ORDER 
        BY NAME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2004
    Posts
    16
    ^ Rudy, that worked like a charm!! Thanks so much *grin*

    Quote Originally Posted by Teddy

    Think in reverse. Don't delete records from your current table, create a table with what you need.
    Teddy, good advice indeed.
    The query Rudy gave me does just that... i guess i was thinking maybe i could modify the delete query to accomplish what i set out to do, but this is prolly safer.

    Thanks all,

    -LK
    Last edited by lastknightess; 09-15-04 at 16:26.

Posting Permissions

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