Re: SQL question

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 - 10:20:47 CET