Re: tricky SQL query (currently handled through PL/SQL)

Strange. How about this:

SQL> desc t1

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 ID                                                 NUMBER(9)
 D1                                                 DATE
 IC                                                 NUMBER(1)

SQL> select id,ic,max(d1),min (d1) from t1 group by id,ic;

        ID IC MAX(D1) MIN(D1) ---------- ---------- --------------- ---------------

         1          1 010103 00:00:04 010103 00:00:00
         1          2 010103 00:00:15 010103 00:00:09
         1          3 010103 00:00:05 010103 00:00:05

• Jusung Yang

jetlag11235_at_yahoo.com (Jetlag) wrote in message news:<2eeed00e.0306050816.72d6beb1_at_posting.google.com>...
> I am working on Oracle 9.2 with the following table:
>
> CREATE TABLE my_table_v1
> (
> my_name NUMBER(9) NOT NULL,
> my_date DATE NOT NULL,
> my_code NUMBER(1) NOT NULL
> );
>
> I would like to create a new table which (for each value of my_name)
> aggregates all of the records with an identical value of my_code that
> are "adjacent" time-wise into a single record with a start (min) and
> an end (max) time. The output table is described below.
>
> CREATE TABLE my_table_v2
> (
> my_name NUMBER(9) NOT NULL,
> my_date1 DATE NOT NULL,
> my_date2 DATE NOT NULL,
> my_code NUMBER(1) NOT NULL
> );
>
> For example, consider this example data in my_table_v1:
>
> 1 '01/01/03 00:00:00' 1
> 1 '01/01/03 00:00:01' 1
> 1 '01/01/03 00:00:04' 1
> 1 '01/01/03 00:00:05' 3
> 1 '01/01/03 00:00:09' 2
> 1 '01/01/03 00:00:15' 2
>
> This should become the following in my_table_v2:
>
> 1 '01/01/03 00:00:00' '01/01/03 00:00:04' 1
> 1 '01/01/03 00:00:05' '01/01/03 00:00:05' 3
> 1 '01/01/03 00:00:09' '01/01/03 00:00:15' 2
>
> Currently I do this through PL/SQL. This involves creating a cursor
> (see below) and then looping through it. I was wondering if any SQL
> gurus could think of a (more efficient) way to do this without using
> PL/SQL.
>
> SELECT my_name,
> my_date,
> my_code,
> FROM my_table_v1
> ORDER BY my_name, my_date;
>
> Thanks for any help.
>
> -- jetlag --
Received on Thu Jun 05 2003 - 23:27:09 CEST