SQL question

[muteki]

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

[drew]

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>

[VC]

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>

[muteki]

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>

[AJ Norman]

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>