Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Question Unanswered: Analytical queries

    Hi,
    I need some help with analytical queries. I have a table with 3 columns:-
    1.ID
    2.GRADE
    3.DT
    The data in this table looks like this:
    -----------------------------------
    1 -----LEVEL 1-----1-Jan-2000
    1 -----LEVEL 1-----1-Feb-2000
    1 -----LEVEL 1-----1-Mar-2000
    1 -----LEVEL 2-----1-Apr-2000
    1 -----LEVEL 2-----1-May-2000
    1 -----LEVEL 3-----1-Jun-2000
    1 -----LEVEL 2-----1-Jul-2000
    2 -----LEVEL 1-----11-Jan-2001
    2 -----LEVEL 1-----11-Feb-2001
    2 -----LEVEL 1-----11-Mar-2001
    2 -----LEVEL 2-----11-Apr-2001
    2 -----LEVEL 3-----11-May-2001
    2-----LEVEL 3-----11-Jun-2001
    2 -----LEVEL 2-----11-Jul-2001
    --------------------
    I need to group this data by ID and GRADE and get the min(date) for each group , but the window needs to be reset for every change in the group(ID,GRADE). so I would like the output to look like this:-
    -------------
    1 -----LEVEL 1-----1-Jan-2000
    1 -----LEVEL 2-----1-Apr-2000
    1 -----LEVEL 3-----1-Jun-2000
    1 -----LEVEL 2-----1-Jul-2000
    2 -----LEVEL 1-----11-Jan-2001
    2 -----LEVEL 2-----11-Apr-2001
    2 -----LEVEL 3-----11-May-2001
    2 -----LEVEL 2-----11-Jul-2001
    Note that LEVEL 2 gets repeated after Level 3 - so if someone goes back to the previous level, then I need to know when that happened. I am hoping there is a way to do this using Oracle AGG/Analytical Functions .Any help is really appreciated. Thanks -JK

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Removing: I didn't finish reading the requirements
    Last edited by beilstwh; 08-01-07 at 10:21.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2006
    Posts
    2

    your problem is in the definition of a group

    this code gives you what you want I think. You should piece together how it works, assuming you like it.

    Code:
    create table kev_t1
    (
     xid number
    ,xgrade varchar2(10)
    ,xdate date
    )
    /
    
    alter session set nls_date_format = 'dd-mon-rrrr';
    
    insert into kev_t1 values (1,'LEVEL 1','1-Jan-2000');
    insert into kev_t1 values (1,'LEVEL 1','1-Feb-2000');
    insert into kev_t1 values (1,'LEVEL 1','1-Mar-2000');
    insert into kev_t1 values (1,'LEVEL 2','1-Apr-2000');
    insert into kev_t1 values (1,'LEVEL 2','1-May-2000');
    insert into kev_t1 values (1,'LEVEL 3','1-Jun-2000');
    insert into kev_t1 values (1,'LEVEL 2','1-Jul-2000');
    insert into kev_t1 values (2,'LEVEL 1','11-Jan-2001');
    insert into kev_t1 values (2,'LEVEL 1','11-Feb-2001');
    insert into kev_t1 values (2,'LEVEL 1','11-Mar-2001');
    insert into kev_t1 values (2,'LEVEL 2','11-Apr-2001');
    insert into kev_t1 values (2,'LEVEL 3','11-May-2001');
    insert into kev_t1 values (2,'LEVEL 3','11-Jun-2001');
    insert into kev_t1 values (2,'LEVEL 2','11-Jul-2001');
    
    commit;
    
    col rowkey format a10
    col prior_rowkey format a10
    
    select a.xid,a.xgrade,a.xdate
    from (
            select xid,xgrade,xdate,xid||'.'||xgrade rowkey,lag(xid||'.'||xgrade) over (order by xdate) prior_rowkey
            from kev_t1
           ) a
    where prior_rowkey is null or prior_rowkey != rowkey
    /
    
    
           XID XGRADE     FIRST_DATE  REAL_GROUP_KEY
    ---------- ---------- ----------- --------------
             1 LEVEL 1    01-jan-2000              1
             1 LEVEL 2    01-apr-2000              2
             1 LEVEL 3    01-jun-2000              3
             1 LEVEL 2    01-jul-2000              4
             2 LEVEL 1    11-jan-2001              5
             2 LEVEL 2    11-apr-2001              6
             2 LEVEL 3    11-may-2001              7
             2 LEVEL 2    11-jul-2001              8
    
    8 rows selected.
    
    SQL> select * from kev_t1 order by xdate;
    
           XID XGRADE     XDATE
    ---------- ---------- -----------
             1 LEVEL 1    01-jan-2000
             1 LEVEL 1    01-feb-2000
             1 LEVEL 1    01-mar-2000
             1 LEVEL 2    01-apr-2000
             1 LEVEL 2    01-may-2000
             1 LEVEL 3    01-jun-2000
             1 LEVEL 2    01-jul-2000
             2 LEVEL 1    11-jan-2001
             2 LEVEL 1    11-feb-2001
             2 LEVEL 1    11-mar-2001
             2 LEVEL 2    11-apr-2001
             2 LEVEL 3    11-may-2001
             2 LEVEL 3    11-jun-2001
             2 LEVEL 2    11-jul-2001
    
    14 rows selected.
    Last edited by km133688; 08-01-07 at 21:45.

  4. #4
    Join Date
    Jul 2007
    Posts
    2
    great !! works wonderfully.

    Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •