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>:
Show quoted text
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:
Show quoted text
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!