In article <6p0t8i$skd_at_bgtnsc03.worldnet.att.net>,
Michael Serbanescu <mserban_at_postoffice.worldnet.att.net> wrote:
> Q1.
>
> Maybe the following query will give you what you're looking for:
>
> SELECT a.Account#, a.Contract#, a.Phone#, a.Phone_Status, a.Activation_Date
> FROM Acct_Hist a
> WHERE a.Activation_Date =
> (SELECT MAX(b.Activation_Date)
> FROM Acct_Hist b
> WHERE b.Account# = a.Account#
> AND b.Contract# = a.Contract#
> AND b.Phone# = a.Phone#);
>
> Q2.
>
> Could you please provide more details about the query you are trying to write
?
>
> Hope this helps.
>
> Michael Serbanescu
>
> dave 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
>
Q2 : If the rows in table B are true duplicates ( all selected fields
are duplicate) do : insert into table c select distinct from table a, table b
-----== 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 - 15:22:47 CDT
