Re: How to delete rows number 2,3,4...

Depending on the significance of the primary key, another option may have
been simply a unique constraint (b,c) on the table before the data was added

On Fri, Oct 8, 2010 at 9:55 PM, A B <gentosaker(at)gmail(dot)com> wrote:

> Thank you all for your replies.
>
>
> 2010/10/8 Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>:
> > On 8 Oct 2010, at 8:59, A B wrote:
> >
> >> Hello.
> >>
> >> I have a table
> >>
> >> create table foo (
> >> a serial,
> >> b int,
> >> c int,
> >> .... more fields ...);
> >>
> >> and now I wish to remove for each combination of b and c, all the
> >> rows except the one with the highest value of a.
> >
> > Or said differently: Delete all the rows where there exists a value of A
> that is higher than the one in the current row, given B and C are equal.
> >
> > In SQL that is:
> >
> > DELETE FROM foo WHERE EXISTS (
> > SELECT 1
> > FROM foo
> > WHERE foo.a > a
> > AND foo.b = bar.b
> > AND foo.c = bar.c
> > )
> >
> > Alban Hertroys
> >
> > --
> > If you can't see the forest for the trees,
> > cut the trees and you'll see there is no forest.
> >
> >
> > !DSPAM:871,4caeeab7678305532215207!
> >
> >
> >
>