Duplicate Rows in Table

I have two Question to you experts out there :

Q1:
How do I select distinct rows from a table (which happens to be a historical table).
The Columns are :-
Account#
Contract#
Phone#
Phone Status. (i.e. AC,CN,VC,VP,VS)
Activation Date.

I wish to group them by phone status - AC -active,CN - cancelled,
(VC+VP+VS) - suspended.

Currently there are duplicate rows eg. Account#,contract#,phone# but with different
phone status. The same (account#,contract#,phone#) that currently exists has multiple phone status i.e. exists in AC and VC or AC and CN.

If I were to do a count ..group by phone status, I am actually picking these figures twice eg. one in AC and the other in VC - not correct.

The activation date determines where it should actually be eg. if there is a phone # which is in AC and VC, the latest activation date will determine where it should actually belong.

Q2:

There is this Table A which does not have duplicate rows. Then there is another Table B which contains duplicate rows. I wish to extract some columns of data from Table A & B to be in Table C. But because Table B consists of duplicate rows, I always get a higher number of rows in Table C
(than Table A.).

Thanking you in advance.

Dave Gill
gurdev_gill_at_hotmail.com Received on Sun Jul 19 1998 - 03:26:54 CDT