Re: SQL Query (Joining the table to itself)

From: Stavros Michailaros <mics_at_netspace.net.au>
Date: 1996/01/27
Message-ID: <4ececn$gt_at_otis.netspace.net.au>#1/1

Pavan Muppidi <v919643_at_raven.cid.kodak.com> wrote:
>Hello,
>
>I have a frequency table as shown below.
>
>frequency Thru_Date Status
>------------------------------------------
> Monthly 01-JAN-96 C
> Monthly 01-FEB-96 I
> Monthly 01-MAR-96 I
> Monthly 01-APR-96 I
> Monthly 01-MAY-96 I
>
>I am trying to write a query to give the following result.
>
>Frequency From_Date To_Date
>----------------------------------------------
> Monthly 01-FEB-96 01-MAR-96
> Monthly 01-MAR-96 01-APR-96
> Monthly 01-APR-96 01-MAY-96
>
>I tried joining the table to itself using rownums, without much success.
>Please somebody help me with a idea how to accomplish this.
>
>Thanks in advance
>
>Pavan Muppidi
>
>
>

I have not tested this, but it should work.

SELECT a.frequency,

       a.thru_date      from_date,
       min(b.thru_date) to_date,
FROM   freq a,
       freq b

WHERE a.frequency = b.frequency
  AND a.thru_date < b.thru_date
GROUP BY a.frequency,

         a.thru_date;

If this does not work, something along these lines should. If the table freq is very large, this query will be very slow. Received on Sat Jan 27 1996 - 00:00:00 CET