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
Received on Tue Jul 21 1998 - 00:05:56 CDT
