Help with Oracle Analytic functions

From: Monty <mmontreaux_at_hotmail.com>
Date: 21 Mar 2003 06:53:05 -0800
Message-ID: <6284dd3.0303210653.6f64bc6@posting.google.com>

Hi everyone, I have a requirement that I think will require the use of Oracle analytic functions. Unfortunately I'm a bit dumbfounded by this. If someone could suggest a solution to my dilemma or point me in the right direction I would be most grateful. Thank you
Monty

CREATE TABLE test(a NUMBER, b NUMBER, c NUMBER);

INSERT INTO test(a,b,c) VALUES (1,1,1);
INSERT INTO test(a,b,c) VALUES (1,3,1);
INSERT INTO test(a,b,c) VALUES (2,1,0);
INSERT INTO test(a,b,c) VALUES (3,6,1);
INSERT INTO test(a,b,c) VALUES (3,8,0);
INSERT INTO test(a,b,c) VALUES (3,9,0);
INSERT INTO test(a,b,c) VALUES (4,16,0);
INSERT INTO test(a,b,c) VALUES (4,21,1);
INSERT INTO test(a,b,c) VALUES (4,61,1);

SELECT a,b,c,SomethingElse1,SomethingElse2 FROM test ORDER BY a ASC, b ASC; What I would like is for SomethingElse1 to be

1. Whenever c is 0, SomethingElse1 is null.
2. Whenever c is 1, SomethingElse1 is the minimum value for b for all other rows that have the same a value.

What I would like is for SomethingElse2 to be  c) SomethingElse2 is the commulative total of b's for all the

    rows that have the same a value when ordered a ASC, b ASC as above.

ie, the output of the query would be:

SQL> 
SQL> 
SQL> 
SQL> 
SQL> SELECT a,b,c,SomethingElse1,SomethingElse2 FROM test ORDER BY a

ASC,b ASC;

         A B C SomethingElse1 SomethingElse2

---------- ---------- ---------- ------------------ -----------------
         1          1          1          1                  1
         1          3          1          1                  4
         2          1          0                             1
         3          6          1          6                  6
         3          8          0                            14
         3          9          0                            23
         4         16          0                            16
         4         21          1         16                 37
         4         61          1         16                 98

9 rows selected.

SQL> We are currently using Oracle 8.1.7 under Windows. Received on Fri Mar 21 2003 - 08:53:05 CST