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@gmail.com> wrote:
Thank you all for your replies.
2010/10/8 Alban Hertroys <dalroi@solfertje.student.utwente.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!
