Thread: How to delete rows number 2,3,4...
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. For example a b c other fields ===== 1 5 5 ..... 2 5 5 .... 3 2 3 .... 4 2 2 ... 5 5 5 ... should leave a b c other fields ===== 3 2 3 .... 4 2 2 ... 5 5 5 ... is there some clever command for this or will I have to write a special function that loops through all values of b and c ?
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!
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! > > >
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:> !DSPAM:871,4caeeab7678305532215207!
>
>> 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.
>
>>
>
>