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