Thread: Select Question
Hi, I need to remove duplicate records from a table like Code Date XS111111 2004-01-26 XS111111 2003-01-22 XS222222 2004-02-01 XS222222 2004-01-26 XS222222 2003-01-22 where only the newest record of Code XSxxxxxxx is kept in the table. Is there an easy way to do that? Thanks Alex
See DISTINCT ON() On Tue, Feb 03, 2004 at 12:22:29AM +0900, Alex wrote: > Hi, > I need to remove duplicate records from a table like > > Code Date > XS111111 2004-01-26 > XS111111 2003-01-22 > XS222222 2004-02-01 > XS222222 2004-01-26 > XS222222 2003-01-22 > > where only the newest record of Code XSxxxxxxx is kept in the table. Is > there an easy way to do that? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
--- Martijn van Oosterhout <kleptog@svana.org> wrote: > See DISTINCT ON() Yes, for selecting. For deleting, probably something like: delete from table t1 where exists (select 1 from table t2 where t2.code = t1.code and t2.date > t1.date); You could write that as a join also. I have found little or no performance difference in the cases that I have tested, although you may find otherwise. BTW, I hope you are not really using "date" as a column name. That is too much trouble to be worth it. > > On Tue, Feb 03, 2004 at 12:22:29AM +0900, Alex > wrote: > > Hi, > > I need to remove duplicate records from a table > like > > > > Code Date > > XS111111 2004-01-26 > > XS111111 2003-01-22 > > XS222222 2004-02-01 > > XS222222 2004-01-26 > > XS222222 2003-01-22 > > > > where only the newest record of Code XSxxxxxxx is > kept in the table. Is > > there an easy way to do that? > > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > (... have gone from d-i being barely usable even > by its developers > > anywhere, to being about 20% done. Sweet. And the > last 80% usually takes > > 20% of the time, too, right?) -- Anthony Towns, > debian-devel-announce > > ATTACHMENT part 2 application/pgp-signature __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/
On Monday 02 February 2004 19:38, Jeff Eckermann wrote: > --- Martijn van Oosterhout <kleptog@svana.org> wrote: > > See DISTINCT ON() > > Yes, for selecting. For deleting, probably something > like: > > delete from table t1 where exists (select 1 from table > t2 where t2.code = t1.code and t2.date > t1.date); Quick note that Jeff's solution doesn't handle the situation where you have codes with the same date. You don't say whether this is possible or not. If it is, you'll need to distinguish between rows based on some other column. -- Richard Huxton Archonet Ltd