Re: SQL question

From: VC <boston103_at_hotmail.com>
Date: Fri, 16 Jan 2004 12:02:56 GMT
Message-ID: <PpQNb.80472$na.43336_at_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" <andrew_toropov_at_hotmail.com> wrote in message news:b71c4ae4.0401160120.644f3887_at_posting.google.com...
> szeming_at_alumni.washington.edu (muteki) wrote in message
 news:<f5cee8fb.0401151833.7ac37f7a_at_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>
Received on Fri Jan 16 2004 - 13:02:56 CET

Original text of this message