Обсуждение: 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

--- 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