Re: SQL question

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" <boston103_at_hotmail.com> wrote in message news:<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 - 21:33:18 CET