Re: SQL question

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.

szeming_at_alumni.washington.edu (muteki) wrote in message news:<f5cee8fb.0401161233.361dc73e_at_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" <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 Mon Jan 19 2004 - 03:03:49 CET