This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jul 13, 2012 1:57 PM by GMoney Go to original post RSS
  • 15. Re: MAX Value for multiple fields in same Query
    GMoney Newbie
    Currently Being Moderated
    I really need some help on this - is there something else I could post that would help?
  • 16. Re: MAX Value for multiple fields in same Query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    GMoney wrote:
    I really need some help on this - is there something else I could post that would help?
    Nothing that hasn't already been said, that is, a test script that the people who want to help you can run to re-create the problem and test their ideas. A test script that is full of mistakes doesn't help very much. Test your code before you post it.
  • 17. Re: MAX Value for multiple fields in same Query
    Boneist Guru
    Currently Being Moderated
    Yes.... more details about how your sample tables above link together please.

    I don't think you've provided enough information to join the ck table accurately - why are there four identical rows? There's no other link to any of the other tables other than by the circuit_design_id, so which row in ck is meant to match to the src table? Could you have more than one product_id number? If so, which one is supposed to appear in the output?

    For ease, I've converted your table / insert statements into a factored subquery
    with ck as (select 'A01' circuit_design_id, '1GSB123456' product_id from dual union all
                select 'A01' circuit_design_id, '1GSB123456' product_id from dual union all
                select 'A01' circuit_design_id, '1GSB123456' product_id from dual union all
                select 'A01' circuit_design_id, '1GSB123456' product_id from dual),
         sr as (select '12345' document_number, to_date('04/04/2006 09:38:08', 'dd/mm/yyyy hh24:mi:ss') order_compl_dt, 'N' activity_ind from dual union all
                select '23456' document_number, to_date('10/04/2008 14:21:28', 'dd/mm/yyyy hh24:mi:ss') order_compl_dt, 'C' activity_ind from dual union all
                select '34567' document_number, to_date('15/07/2008 10:14:32', 'dd/mm/yyyy hh24:mi:ss') order_compl_dt, 'D' activity_ind from dual union all
                select '45678' document_number, to_date('03/03/2009 14:55:55', 'dd/mm/yyyy hh24:mi:ss') order_compl_dt, 'D' activity_ind from dual),
        src as (select 'A01' circuit_design_id, '12345' document_number from dual union all
                select 'A01' circuit_design_id, '23456' document_number from dual union all
                select 'A01' circuit_design_id, '34567' document_number from dual union all
                select 'A01' circuit_design_id, '45678' document_number from dual)
    select *
    from   ck,
           sr,
           src
    where  ck.circuit_design_id = src.circuit_design_id
    and    src.document_number = sr.document_number;
    Once you've got the structure and data decided, my guess is that you'll be after an aggregated dense_rank keep first/last function to get the values of the top row that you appear to be after.
  • 18. Re: MAX Value for multiple fields in same Query
    GMoney Newbie
    Currently Being Moderated
    Boniest - thanks for the reply.

    The tables link together just as you show.
    where  ck.circuit_design_id = src.circuit_design_id
    and    src.document_number = sr.document_number;
    The SRC table is for the most part a Bridge table although in the real application it does contain other fields.
    I am not sure how else to explain it. :(

    This is as close as I have gotten:
    SELECT CK.CIRCUIT_DESIGN_ID,
           MAX(SRC.DOCUMENT_NUMBER),
           CK.PRODUCT_ID,
           SR.ACTIVITY_IND,
           MAX(SR.ORDER_COMPL_DT) KEEP (DENSE_RANK LAST ORDER BY SRC.DOCUMENT_NUMBER)
      FROM    (   SRC
               INNER JOIN
                  SR
               ON (SRC.DOCUMENT_NUMBER = SR.DOCUMENT_NUMBER))
           INNER JOIN
              CK
           ON (CK.CIRCUIT_DESIGN_ID = SRC.CIRCUIT_DESIGN_ID)
      GROUP BY  CK.CIRCUIT_DESIGN_ID,
                SR.ACTIVITY_IND,
                CK.PRODUCT_ID
    Results being:
    CIRCUIT_DESIGN_ID     DOCUMENT_NUMBER     PRODUCT_ID     ACTIVITY_IND     ORDER_COMPL_DT
    A01     23456     1GSB123456     C     4/10/2008 2:21:28 PM
    A01     45678     1GSB123456     D     3/3/2009 2:55:55 PM
    A01     12345     1GSB123456     N     4/4/2006 9:38:08 AM
    With this being the desired out come example:
    CIRCUIT_DESIGN_ID     DOCUMENT_NUMBER     PRODUCT_ID     ACTIVITY_IND     ORDER_COMPL_DT
    A01                             45678     1GSB123456                   D     3/3/2009 2:55:55 PM
  • 19. Re: MAX Value for multiple fields in same Query
    GMoney Newbie
    Currently Being Moderated
    I believe this is correct:
    create table CK (CIRCUIT_DESIGN_ID VARCHAR(10),
         Product_ID VARCHAR(10));
    
    INSERT INTO Design VALUES('A01','1GSB123456');
    INSERT INTO Design VALUES('A01','1GSB123456');
    INSERT INTO Design VALUES('A01','1GSB123456');
    INSERT INTO Design VALUES('A01','1GSB123456');
    
    create table SR (DOCUMENT_NUMBER VARCHAR(10),
              ORDER_COMPL_DT  VARCHAR(25),
              ACTIVITY_IND VARCHAR(4));
    
    INSERT INTO Order VALUES('12345','4/4/2006 9:38:08 AM','N');
    INSERT INTO Order VALUES('23456','4/10/2008 2:21:28 PM','C');
    INSERT INTO Order VALUES('34567','7/15/2008 10:17:32 AM','D');
    INSERT INTO Order VALUES('45678','3/3/2009 2:55:55 PM','D');
    
    
    create table SRC (CIRCUIT_DESIGN_ID VARCHAR(10),
         DOCUMENT_NUMBER VARCHAR(10));
         
    INSERT INTO Design VALUES('A01','12345');
    INSERT INTO Design VALUES('A01','23456');
    INSERT INTO Design VALUES('A01','34567');
    INSERT INTO Design VALUES('A01','45678');
    thanks again
  • 20. Re: MAX Value for multiple fields in same Query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    GMoney wrote:
    I believe this is correct: ...
    Really? It looks just like what you posted 3 hours ago. The tables in the CREATE TABLE statements are not the same as the tables in the INSERT statements, you're still using ORDER as a table name, and it looks like you're trying to INSERT a VARCHAR2 into a DATE column.
  • 21. Re: MAX Value for multiple fields in same Query
    GMoney Newbie
    Currently Being Moderated
    Sorry - wrong Notepad used -
    create table SRC (CIRCUIT_DESIGN_ID VARCHAR(10),
         DOCUMENT_NUMBER VARCHAR(10));
         
    INSERT INTO SRC VALUES('A01','12345');
    INSERT INTO SRC VALUES('A01','23456');
    INSERT INTO SRC VALUES('A01','34567');
    INSERT INTO SRC VALUES('A01','45678');
    
    create table SR (DOCUMENT_NUMBER VARCHAR(10),
              ORDER_COMPL_DT  VARCHAR(2),
              ACTIVITY_IND VARCHAR(4));
     
    INSERT INTO SR VALUES('12345',to_date('04/04/2006 09:38:08', 'dd/mm/yyyy hh24:mi:ss'),'N');
    INSERT INTO SR VALUES('23456',to_date('10/04/2008 14:21:28', 'dd/mm/yyyy hh24:mi:ss'),'C');
    INSERT INTO SR  VALUES('34567',to_date('15/07/2008 10:14:32', 'dd/mm/yyyy hh24:mi:ss'),'D');
    INSERT INTO SR  VALUES('45678',to_date('03/03/2009 14:55:55', 'dd/mm/yyyy hh24:mi:ss'),'D');
    
    create table CK (CIRCUIT_DESIGN_ID VARCHAR(10),
         Product_ID VARCHAR(10));
     
    INSERT INTO CK VALUES('A01','1GSB123456');
    INSERT INTO CK VALUES('A01','1GSB123456');
    INSERT INTO CK VALUES('A01','1GSB123456');
    INSERT INTO CK VALUES('A01','1GSB123456');
    I am so focused on trying to resolve my issue that I grabbed the wrong script.

    Thanks again for looking.

    G
  • 22. Re: MAX Value for multiple fields in same Query
    GMoney Newbie
    Currently Being Moderated
    The solution I came up with is as follows - for those that maybe needing something like this in the future :).
    No doubt one of the Guru's will probably have a better way, but hey it works -

    SELECT CK.CIRCUIT_DESIGN_ID,
           MAX(SRC.DOCUMENT_NUMBER),
           CK.PRODUCT_ID,
           MAX(SR.ORDER_COMPL_DT),
           MAX(DECODE (SR.ACTIVITY_IND,
                      'C', 'C - Change',
                      'D', 'D - Disconnect',
                      'N', 'N - New Installation',
                      '', '(None)')) KEEP (DENSE_RANK LAST ORDER BY SR.ORDER_COMPL_DT)ACT_IND
      FROM    (   SRC
               INNER JOIN
                  SR
               ON (SRC.DOCUMENT_NUMBER = SR.DOCUMENT_NUMBER))
           INNER JOIN
              CK
           ON (CK.CIRCUIT_DESIGN_ID = SRC.CIRCUIT_DESIGN_ID)
      GROUP BY  CK.CIRCUIT_DESIGN_ID,
                CK.PRODUCT_ID
    results;
    CIRCUIT_DESIGN_ID     MAX(SRC.DOCUMENT_NUMBER)     PRODUCT_ID     MAX(SR.ORDER_COMPL_DT)     ACT_IND
    A01                                             45678     1GSB123456                        3/3/2009     D - Disconnect
  • 23. Re: MAX Value/Multiple Fields and PIVOT
    GMoney Newbie
    Currently Being Moderated
    Figured it out.. Thanks to all that replied.
  • 24. Re: MAX Value for multiple fields in same Query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    GMoney wrote:
    ...
    create table SR (DOCUMENT_NUMBER VARCHAR(10),
              ORDER_COMPL_DT  VARCHAR(2),
              ACTIVITY_IND VARCHAR(4));
    
    INSERT INTO SR VALUES('12345',to_date('04/04/2006 09:38:08', 'dd/mm/yyyy hh24:mi:ss'),'N');
    INSERT INTO SR VALUES('23456',to_date('10/04/2008 14:21:28', 'dd/mm/yyyy hh24:mi:ss'),'C');
    INSERT INTO SR  VALUES('34567',to_date('15/07/2008 10:14:32', 'dd/mm/yyyy hh24:mi:ss'),'D');
    INSERT INTO SR  VALUES('45678',to_date('03/03/2009 14:55:55', 'dd/mm/yyyy hh24:mi:ss'),'D');
    Good; you're correctly using TO_DATE.
    Why did you change the data type for irder_compl_dt to VARCHAR (2)?

     

    Do all the joins, and any other filtering you may need, inside the sub-query were the ROW_NUMEBR is computed, if not earlier.
    The main query will do nothing except filter by rn (the results of ROW_NUMBER), and ORDER BY, if you want sorted output.
    WITH     got_rn          AS
    (
         SELECT     ck.circuit_design_id
         ,     sr.document_number
         ,     ck.product_id
         ,     sr.activity_ind
         ,     sr.order_compl_dt
         ,     ROW_NUMBER () OVER ( PARTITION BY  ck.circuit_design_id
                                   ORDER BY          sr.document_number     -- or sr.order_compl_dt
                                       DESC
                           ) AS rn
         FROM    ck
         JOIN     src     ON     ck.circuit_design_id     = src.circuit_design_id
         JOIN     sr     ON     src.document_number     = sr.document_number
    --     WHERE     ...     -- If you need other filtering, put it here
    )
    SELECT       circuit_design_id
    ,       document_number
    ,       product_id
    ,       activity_ind
    ,       order_compl_dt
    FROM       got_rn
    WHERE       rn     = 1
    ORDER BY  circuit_design_id     -- if wanted
    ;
    If higher document numbers always correspond to later order_compl_dts, then it doesn't matter which of those columns you use in the analytic ORDER BY clause. Either way, rememeber to specify <b>DESC</b>ending order.
  • 25. Re: MAX Value for multiple fields in same Query
    GMoney Newbie
    Currently Being Moderated
    Frank

    Thanks for the followup - and the push.
    As I said in my last posts what I came up with works. My question is now, is it wrong?

    It seems to run well as far as time is concerned but then again I have no idea how to "tune" a query so I may be way off.


    Thanks again,

    Greg
  • 26. Re: MAX Value for multiple fields in same Query
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Greg,
    GMoney wrote:
    ... As I said in my last posts what I came up with works. My question is now, is it wrong?
    No, it's not wrong.

    Depending on your indexes, I suspect that ROW_NUMBER will be faster, but the difference may not be significant.
    It seems to run well as far as time is concerned
    Apparently it's not significant.

    You're relying on the fact that higher document_numebrs always correspond to higher order_compl_dts. If that ever changes in the future, then you'll have to change the query. For example, if you want the document_number that is related to the latest order_compl_dt, even if it isn't the highest document_number, then you'll have to change:
           MAX(SRC.DOCUMENT_NUMBER)
    to
           MAX(SRC.DOCUMENT_NUMBER) KEEP (DENSE_RANK LAST ORDER BY SR.ORDER_COMPL_DT)
    In your real problem, are there more columns like this, or like sr_activity_ind, where you're interested in the value from the "last" row (however you define "last")? If so, you'll need to use "MAX (...) KEEP (DENSE_RANK LAST ...)" for all of them, which will be hard to maintain and to debug. Using ROW_NUMBER, selecting more columns related to the latest row is simpler.

    I personally find LAST harder to understand and debug than ROW_NUMBER.
    Other people, with a lot of experience and intelligence, may fell just the opposite. It looks like Boneist is one of them.
    I'm sure there are many people who think ROW_NUMBER and LAST are equally bewildering and incomprehensible. I would suggest that they use ROW_NUMBER. Whatever time they invest in learning how to use ROW_NUMBER will pay off in using other analytic fucntions in the future. Of course, it's good to know how to use LAST, too, but I think anyone with Oracle experience will agree there are more jobs where analytic fucntions are useful than there are jobs where LAST is useful.
  • 27. Re: MAX Value for multiple fields in same Query
    GMoney Newbie
    Currently Being Moderated
    Frank Kulash wrote:
    You're relying on the fact that higher document_numbers always correspond to higher order_compl_dts. If that ever changes in the future, then you'll have to change the query. For example, if you want the document_number that is related to the latest order_compl_dt, even if it isn't the highest document_number, then you'll have to change:
     
    MAX(SRC.DOCUMENT_NUMBER) 
    to
     
    MAX(SRC.DOCUMENT_NUMBER) KEEP (DENSE_RANK LAST ORDER BY SR.ORDER_COMPL_DT) 
    Frank

    Ok - I see your good point. I am relying on what I have seen "visually" in the data, but Yes there may be a possibility where it does not correlate exactly.
    I believe I will rework the query based on what you have shown for two reasons:

    1. I want to know how to, and understand using Row_Number.
    2. To care for the unknown's that may arise.


    Thanks again for all of your help, I really do appreciate it.


    Greg
1 2 Previous Next

Legend

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