var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Removing Duplicates
I have a duplicates query, and I want to see only unique records with their most recent date.
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.
hi, i'm afraid i need a little more help
Originally Posted by
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
WHERE (((AMO.NAME) In (SELECT [NAME] FROM [AMO] As Tmp GROUP BY [NAME] HAVING Count(*)>1 )))
ORDER BY AMO.NAME;
select name, area, lce
from amo as X
where name = (select name
group by name
I think this should return the values you want.
^ i get an error message: "at most one record can be returned from this subquery" and then it does nothing.
any idea why?
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...
Think in reverse. Don't delete records from your current table, create a table with what you need.
SELECT DISTINCT whatever INTO newTable
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.
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:
FROM AMO as x
= (select max(LCE)
where NAME = x.NAME )
^ Rudy, that worked like a charm!! Thanks so much *grin*
Teddy, good advice indeed.
Originally Posted by
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.
Last edited by lastknightess; 09-15-04 at