Re: How to delete rows number 2,3,4... - Mailing list pgsql-general

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!