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






---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

See DISTINCT ON()

On Tue, Feb 03, 2004 at 12:22:29AM +0900, Alex wrote:[color=blue]
> 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?[/color]

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/[color=blue]
> (... 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[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAHp++Y5Twig3Ge+YRAo76AKCbQqjiZTFdvXXUMf+Adr 7tU4ooiACfVX6b
C9lCsUnoOOD9Y8hADTN8BWc=
=5Jbw
-----END PGP SIGNATURE-----

--- Martijn van Oosterhout <kleptog@svana.org> wrote:[color=blue]
> See DISTINCT ON()[/color]

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.
[color=blue]
>
> On Tue, Feb 03, 2004 at 12:22:29AM +0900, Alex
> wrote:[color=green]
> > Hi,
> > I need to remove duplicate records from a table[/color]
> like[color=green]
> >
> > 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[/color]
> kept in the table. Is[color=green]
> > there an easy way to do that?[/color]
>
> --
> Martijn van Oosterhout <kleptog@svana.org>
> http://svana.org/kleptog/[color=green]
> > (... have gone from d-i being barely usable even[/color]
> by its developers[color=green]
> > anywhere, to being about 20% done. Sweet. And the[/color]
> last 80% usually takes[color=green]
> > 20% of the time, too, right?) -- Anthony Towns,[/color]
> debian-devel-announce
>[/color]
[color=blue]
> ATTACHMENT part 2 application/pgp-signature[/color]



__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Monday 02 February 2004 19:38, Jeff Eckermann wrote:[color=blue]
> --- Martijn van Oosterhout <kleptog@svana.org> wrote:[color=green]
> > See DISTINCT ON()[/color]
>
> 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);[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html