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

[Ben Carbery <ben.carbery@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

Thank you all for your replies.


2010/10/8 Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>:

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

>
>
>