Thanks, Larry. Great tip! On Fri, Jul 18, 2008 at 10:14 AM, Lawrence Lustig <[EMAIL PROTECTED]> wrote: > << > Does anyone have an idea as to how R:Base decides which rows get > deleted using this command? Is it first in, last out, based on time > stamp or something? Or do I use an ORDER BY to sort it out? >>> > > When using DELETE DUPLICATES without the USING clause the records are > _exactly_ identical, so it doesn't matter which one stays and which go. > > When using USING R:Base will make it's own decision about which to keep. I > imagine this will _usually_ be the first record in natural table order but I > wouldn't count on it. If the data is different in some columns and the > record you want to keep is important, I would do your own delete. > > You can do this with something like > > DELETE FROM MyTable MT1 WHERE NOT EXISTS (SELECT * FROM MyTable MT2 WHERE > MT2.CaseNo = MT1.CaseNo AND MT2.CaseDate < MT1.CaseDate) > > This is will keep the "most recent" record (by CaseDate) for each CaseNo. > Assuming CaseNo is indexed, it will be quite fast. The only problem is that > if you have two CaseNo records for the SAME CaseDate, they'll both be kept. > You can modify this idea to suit your particular circumstances. > -- > Larry > > >
-- John Croson [EMAIL PROTECTED] http://pcnorb.blogspot.com/ http://pcnorb.homelinux.org/
