Re: Duplicate Rows

There are many ways to do Q1, and I am sure several will be presented.

Although you didnot specify the primary key for your table, I will give you one basic approach:

create view max_date as
select Account#,Contract#,Phone#,max(Activation_Date) "max_act_date" from table
group by Account#,Contract#,Phone#

        This should give you a unique entry of account#,contract#,phone#
        with its max activation date. Now use this view to select you data...

select phone_status, account#,contract#,phone# from table, max_date
where max_act_date=activation_date
group by phone_status,account#,contract#,phone#

You might even be able to use:
select phone_status, account#,contract#,phone# from table a
where max_act_date=(select max(activation_date)

    from table b
    where a.phone#=b.phone#
    and a.account#=b.account# and a.contract#=b.contract#     )
group by phone_status,account#,contract#,phone#

NOTE: These are just general ideas...Hope it helps...

-Frank

In article <01bdb3f3$b14ed3c0$b0658ea1_at_atanc>,   "dave" <gurdev_gill_at_hotmail.com> wrote:
> 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
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Jul 21 1998 - 08:01:43 CDT