P: n/a

Hi,
I am pretty new to SQL and I would like to know is it possible to do
the following task in SQL?
I have a table containing 2 columns A, B where A is the primary key.
If the table contains the following data.
A B
 
1 a
2 b
3 b
4 b
5 c
6 c
7 d
I would like to run a sql statement to get rid of all the rows
containing duplicated entries of B where the smaller A will get
deleted. (keep the last row where there is no longer duplicates of B)
The end result will be
A B
 
1 a
4 b
6 c
7 d
Thanks,
muteki  
Share this Question
P: n/a
 sz*****@alumni.washington.edu (muteki) wrote in message news:<f5**************************@posting.google. com>... Hi,
I am pretty new to SQL and I would like to know is it possible to do the following task in SQL?
I have a table containing 2 columns A, B where A is the primary key. If the table contains the following data.
A B   1 a 2 b 3 b 4 b 5 c 6 c 7 d
I would like to run a sql statement to get rid of all the rows containing duplicated entries of B where the smaller A will get deleted. (keep the last row where there is no longer duplicates of B)
The end result will be
A B   1 a 4 b 6 c 7 d
Thanks,
muteki
SQL> select * from aa;
A B
 
1 a
2 b
3 b
4 b
5 c
6 c
7 d
7 rows selected.
SQL> select max(a) "a",b from aa where b not in
2 (select b from aa group by b having count(*)>1 ) group by a,b
3 union
4 select max(a) "a",b from aa group by b having count(*)>1;
a B
 
1 a
4 b
6 c
7 d
SQL>  
P: n/a

Hello,
It's much simpler:
select * from t1 where (a,b) in (select max(a), b from t1 group by b)
Rgds.
"drew" <an************@hotmail.com> wrote in message
news:b7**************************@posting.google.c om... sz*****@alumni.washington.edu (muteki) wrote in message
news:<f5**************************@posting.google. com>... Hi,
I am pretty new to SQL and I would like to know is it possible to do the following task in SQL?
I have a table containing 2 columns A, B where A is the primary key. If the table contains the following data.
A B   1 a 2 b 3 b 4 b 5 c 6 c 7 d
I would like to run a sql statement to get rid of all the rows containing duplicated entries of B where the smaller A will get deleted. (keep the last row where there is no longer duplicates of B)
The end result will be
A B   1 a 4 b 6 c 7 d
Thanks,
muteki
SQL> select * from aa;
A B   1 a 2 b 3 b 4 b 5 c 6 c 7 d
7 rows selected.
SQL> select max(a) "a",b from aa where b not in 2 (select b from aa group by b having count(*)>1 ) group by a,b 3 union 4 select max(a) "a",b from aa group by b having count(*)>1;
a B   1 a 4 b 6 c 7 d
SQL>  
P: n/a

Thanks for the ideas. Instead of querying the end result, how can I
operate on the table such that it really deletes the duplicates? That
is, delete everything that is not in your select statement?
muteki
"VC" <bo*******@hotmail.com> wrote in message news:<PpQNb.80472$na.43336@attbi_s04>... Hello,
It's much simpler:
select * from t1 where (a,b) in (select max(a), b from t1 group by b)
Rgds.
"drew" <an************@hotmail.com> wrote in message news:b7**************************@posting.google.c om... sz*****@alumni.washington.edu (muteki) wrote in message news:<f5**************************@posting.google. com>... Hi,
I am pretty new to SQL and I would like to know is it possible to do the following task in SQL?
I have a table containing 2 columns A, B where A is the primary key. If the table contains the following data.
A B   1 a 2 b 3 b 4 b 5 c 6 c 7 d
I would like to run a sql statement to get rid of all the rows containing duplicated entries of B where the smaller A will get deleted. (keep the last row where there is no longer duplicates of B)
The end result will be
A B   1 a 4 b 6 c 7 d
Thanks,
muteki
SQL> select * from aa;
A B   1 a 2 b 3 b 4 b 5 c 6 c 7 d
7 rows selected.
SQL> select max(a) "a",b from aa where b not in 2 (select b from aa group by b having count(*)>1 ) group by a,b 3 union 4 select max(a) "a",b from aa group by b having count(*)>1;
a B   1 a 4 b 6 c 7 d
SQL>
 
P: n/a

Here is one way using analytics.
SQL> select * from tt;
X Y
 
1 a
2 b
3 b
4 b
5 c
6 c
7 d
delete from tt
where x in (select x
from ( select x,y,row_number() over (partition by y order
by x desc) rn from tt)
where rn<>1);
3 rows deleted
SQL> select * from tt;
X Y
 
1 a
4 b
6 c
7 d
Andre. sz*****@alumni.washington.edu (muteki) wrote in message news:<f5**************************@posting.google. com>... Thanks for the ideas. Instead of querying the end result, how can I operate on the table such that it really deletes the duplicates? That is, delete everything that is not in your select statement?
muteki
"VC" <bo*******@hotmail.com> wrote in message news:<PpQNb.80472$na.43336@attbi_s04>... Hello,
It's much simpler:
select * from t1 where (a,b) in (select max(a), b from t1 group by b)
Rgds.
"drew" <an************@hotmail.com> wrote in message news:b7**************************@posting.google.c om... sz*****@alumni.washington.edu (muteki) wrote in message news:<f5**************************@posting.google. com>... > Hi, > > I am pretty new to SQL and I would like to know is it possible to do > the following task in SQL? > > I have a table containing 2 columns A, B where A is the primary key. > If the table contains the following data. > > A B >   > 1 a > 2 b > 3 b > 4 b > 5 c > 6 c > 7 d > > I would like to run a sql statement to get rid of all the rows > containing duplicated entries of B where the smaller A will get > deleted. (keep the last row where there is no longer duplicates of B) > > The end result will be > > A B >   > 1 a > 4 b > 6 c > 7 d > > Thanks, > > muteki
SQL> select * from aa;
A B   1 a 2 b 3 b 4 b 5 c 6 c 7 d
7 rows selected.
SQL> select max(a) "a",b from aa where b not in 2 (select b from aa group by b having count(*)>1 ) group by a,b 3 union 4 select max(a) "a",b from aa group by b having count(*)>1;
a B   1 a 4 b 6 c 7 d
SQL>   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 1787
 replies: 4
 date asked: Jul 19 '05
