From: boston103@hotmail.com   
      
   Hello,   
      
   It's much simpler:   
      
   select * from t1 where (a,b) in (select max(a), b from t1 group by b)   
      
      
   Rgds.   
      
      
   "drew"  wrote in message   
   news:b71c4ae4.0401160120.644f3887@posting.google.com...   
   > szeming@alumni.washington.edu (muteki) wrote in message   
   news:...   
   > > 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>   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)