This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 1, 2011 8:18 AM by TinyPenguin Go to original post RSS
  • 15. Re: Ranking query
    TinyPenguin Newbie
    Currently Being Moderated
    Hi Frank,

    Yes, as usual you're totally correct. I misread the query when I was just thinking of max(start/end date) as an aggregate as opposed to it's analytical counterpart max(end date) over() as you provided. But yes, you mentioned that you can't nest analytics when you first started helping me with this.

    This has been great. I've really learned something about analytics I didn't know before. Thanks for that. Top stuff.
  • 16. Re: Ranking query
    TinyPenguin Newbie
    Currently Being Moderated
    Hello,

    Me again...

    I was wondering, is it possible to use analytical functions (maybe using Keep?) to get the value of another field associated with a subquery's min/max start/end dates (i.e. the name of the person associated with the min/max value) for periods?

    For example, for period 1, is it possible to return the teacher at the start of the period was 'Mr Smith' and the teacher at the end of the period was 'Mrs Allan' in the same row as the min/max start/end date?

    The rows returned would look like:

    pid, startdate1, enddate1, first teacher, last teacher
    pid, startdate2, enddate2, first teacher, last teacher


    If not, I'd be interested to know how you would get the teachers associated with the start and end of the period. I kind of feel that the way I'd do it - self joining the query to the original table based on PID and startdate/enddate -  is wrong?

    Thanks alot. :)


    Create table mytable4
    (PID varchar(10),
    Startdate date,
    enddate date,
    teacher varchar(20),
    StartCode varchar(1),
    EndCode varchar(1),
    desired varchar(2));

    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('11/12/2008','DD/MM/RRRR'), to_date

    ('24/12/2008','DD/MM/RRRR'),'Mr Smith' ,'S','X',1);
    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('24/12/2008','DD/MM/RRRR'), to_date

    ('22/05/2009','DD/MM/RRRR'),'Mr Smith' ,'P','X',1);
    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('22/05/2009','DD/MM/RRRR'), to_date

    ('25/11/2009','DD/MM/RRRR'),'Mrs Allan' ,'P','E',1);
    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('04/05/2010','DD/MM/RRRR'), to_date

    ('17/06/2010','DD/MM/RRRR'),'Mr Johnson' ,'S','E',2);
    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('17/06/2010','DD/MM/RRRR'), to_date

    ('19/07/2010','DD/MM/RRRR'),'Mr Coates','B','E',3);
    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('29/09/2010','DD/MM/RRRR'), to_date

    ('23/11/2010','DD/MM/RRRR'),'Mrs Jackson','S','E',4);
    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('14/01/2011','DD/MM/RRRR'), to_date

    ('20/04/2011','DD/MM/RRRR'),'Mr Watson','P','E',4);
    insert into mytable4 (PID, StartDate, Enddate, Teacher, StartCode, EndCode, desired) values ('A657', to_date('20/04/2011','DD/MM/RRRR'), null, 'Mr

    Robson','B',null,5);
  • 17. Re: Ranking query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Tiny Penguin wrote:
    Hello,

    Me again...
    Not very many people (who haven't already participated in it) are going to read a thread that already has 16 replies. If you have a new variation on an old problem, like this, I think you'll get better response if you start a new thread. You can include a link to the original thread, if you think that will help.
    I was wondering, is it possible to use analytical functions (maybe using Keep?) to get the value of another field associated with a subquery's min/max start/end dates (i.e. the name of the person associated with the min/max value) for periods?

    For example, for period 1, is it possible to return the teacher at the start of the period was 'Mr Smith' and the teacher at the end of the period was 'Mrs Allan' in the same row as the min/max start/end date?
    KEEP is a keyword (not a function) that is used with the FIRST and LAST functions. If you want to collapse the results down to one row for every distinct combination of pid and period, then aggregate FIRST and LAST is a good way to do it, especially since the column used for ordering (that is, startdate) is unique for each pid.
    WITH     got_period     AS
    (
         SELECT     m.*
         ,     COUNT ( CASE
                             WHEN  startcode IN ('B', 'S')
                       THEN  1
                           ELSE  NULL     -- This is the default, but you can explicitly say it if you want to
                         END
                       )     OVER ( PARTITION BY  pid
                                ORDER BY          startdate
                        )     AS period
         FROM     mytable4     m
    )
    SELECT       pid
    ,       MIN (startdate)                              AS period_startdate
    ,       MAX (enddate)                                   AS period_enddate
    ,       MIN (teacher) KEEP (DENSE_RANK FIRST ORDER BY  startdate)     AS first_teacher
    ,       MIN (teacher) KEEP (DENSE_RANK LAST  ORDER BY  startdate)     AS last_teacher
    FROM       got_period
    GROUP BY  pid, period
    ORDER BY  pid, period
    ;
    This assumies that you need to derive period, instead of using the desired column in the table.

    I hear you asking "Why did you use *MIN* to get the <b>last</b>_teacher? Shouldn't that be MAX?" In this problem, it actually doesn't matter if we use MIN or MAX for either of those columns. The important thing is that we use FIRST and LAST correctly. The aggregate function at the beginning of the expression is just a tie-breaker. "MIN (teacher) KEEP (DENSE_RANK LAST ORDER BY startdate)" means "Find the *LAST* teacher in order by startdate. If there happens to be a tie (multiple teachers with the same startdate), then take the mimimum values of the contenders." You've already said that ties are impossible in your case, so you'll never have more than 1 contender, and the MIN will always be the same as the MAX.

    If you didn't want to collapse the result set (that is, if you wanted one row of output for every row in the original table), then you could use the analytic FIRST_VALUE function.
    The rows returned would look like:

    pid, startdate1, enddate1, first teacher, last teacher
    pid, startdate2, enddate2, first teacher, last teacher
    Do you want the output to say 'first teacher' and 'last teacher', or do you want it to say things like 'Mr Smith' and 'Mrs Allan'? Please post the exact results that you want, and/or include the desired results in your INSERT statements, like you did before. (Including the desired results in your table is very hady; it makes it easy to see if the answer is right or not.)
    The results from the query above are:
    PID  PERIOD_STA PERIOD_END FIRST_TEACHER LAST_TEACHER
    ---- ---------- ---------- ------------- ------------
    A657 11/12/2008 25/11/2009 Mr Smith      Mrs Allan
    A657 04/05/2010 17/06/2010 Mr Johnson    Mr Johnson
    A657 17/06/2010 19/07/2010 Mr Coates     Mr Coates
    A657 29/09/2010 20/04/2011 Mrs Jackson   Mr Watson
    A657 20/04/2011            Mr Robson     Mr Robson
    If not, I'd be interested to know how you would get the teachers associated with the start and end of the period. I kind of feel that the way I'd do it - self joining the query to the original table based on PID and startdate/enddate -  is wrong?
    You have good instincts! Just curious, why do you feel that approach is wrong?
    I'm not sure I'd call it wrong. You could certainly get the right results that way. I suspect a self-join would be a little less efficient than what I posted above, but that's just a guess. I haven't looked at the execution plans, or done any tests.
  • 18. Re: Ranking query
    TinyPenguin Newbie
    Currently Being Moderated
    Hi Frank, thanks. That works brilliantly. I want to reply more fully but can only access forum over phone at the mo but just wanted to let you know it works excellently.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points