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

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:737,4caeeabc678306717112265!