If the other solutions perform as well or better, I would definitely go with one of them. My solution is ugly and not scaleable.
On Sun, Feb 13, 2011 at 1:24 AM, gayathri Dev <gd0...@gmail.com> wrote: > hahaha > Fact is - even thou its using Max and substr, which are a familiar ones.. > it took a lil longer for me to understand. ( You are the Master Mike ) > somehow was happy tht i found 2 solutions...and left the 3rd one in > between..:)) > will learn that one too... > > Thanks again to the group. > > > > > > > On Fri, Feb 11, 2011 at 11:52 PM, Michael Moore > <michaeljmo...@gmail.com>wrote: > >> So I guess that means you didn't like my solution. ;-) >> Mike >> >> >> On Fri, Feb 11, 2011 at 9:10 PM, gayathri Dev <gd0...@gmail.com> wrote: >> >>> Thanks All for the suggestions and a clear detailed explanations. >>> >>> I tried both Rich's and Andrej's suggestion. It works..:-) >>> >>> "Keep" is new to me. Thanks for introducing. >>> >>> >>> >>> On Fri, Feb 11, 2011 at 11:41 AM, Richard Pascual <richg...@gmail.com>wrote: >>> >>>> I got this idea from the PSOUG site which describes the different ways >>>> analytical functions can be leveraged. Apparently MAX is one such function >>>> which can be used with a "partition by" directive. Forgive any initial >>>> mistakes as I have yet to test. Just wanted to get an idea out to bounce >>>> amongst our group. >>>> >>>> -- begin query >>>> >>>> select action_results.action_flag from ( >>>> >>>> select p.action_flag, >>>> p.rpt_num, >>>> p.participant_order, >>>> max(p.participant_order) over ( partition by p.rpt_num ) as >>>> max_participant_order >>>> from participants p >>>> where p.role_ind between 1 and 20 ) action_results >>>> >>>> where action_results.participant_order = >>>> action_results.max_participant_order >>>> and action_results.rpt_num = 311118 >>>> >>>> -- end query >>>> >>>> discussion: >>>> my approach still involves a subquery, but I think it is clearer to >>>> understand. The functionality of this SQL snippet is twofold: >>>> >>>> (1) the inner subquery creates a selection of action flags and rpt_num >>>> (report id's) for which the role_ind is between 1 and 20 >>>> >>>> (2) the outer subquery further refines the search to limit the results >>>> to just the action flags for which the participant order is the "max" value >>>> for that given report id and also to where the report id = 311118 >>>> >>>> Now if you were to integrate this into a PL/SQL environment such as a >>>> stored procedure or function, you could parametrize the rpt_num reference >>>> (311118) with a parameter value. I structured the sql query with this in >>>> mind. As it is written, the query only requires the rpt_num reference to be >>>> coded ONCE as opposed to TWICE in the original query concept attempted by >>>> the poster, Gayathri. >>>> >>>> Requiring the same parameter to be used multiple times in the same query >>>> often yields a danger of mistakes (I know "search-and-replace" might >>>> prevent >>>> this, but not always, especially in large programs) as you would have to >>>> exhaustively read through all the code to find all references to rpt_num. >>>> In >>>> my example, you would only need to change that value in one place. >>>> >>>> I really like where Oracle is going with the analytical functions... >>>> while I am relatively new at using them, their utility is already paying >>>> dividends in my coding! >>>> >>>> Rich Pascual >>>> >>>> >>>> >>>> Some great info on Oracle analytical functions: >>>> http://psoug.org/reference/analytic_functions.html >>>> >>>> >>>> >>>> >>>> >>>> Rich Pascual >>>> >>>> >>>> On Fri, Feb 11, 2011 at 11:12 AM, Michael Moore < >>>> michaeljmo...@gmail.com> wrote: >>>> >>>>> Good catch Rich, I assumed they were. >>>>> Mike >>>>> >>>>> >>>>> On Fri, Feb 11, 2011 at 11:06 AM, Richard Pascual >>>>> <richg...@gmail.com>wrote: >>>>> >>>>>> Are the columns, p_order and participant_order different columns? >>>>>> >>>>>> Rich Pascual >>>>>> >>>>>> >>>>>> >>>>>> On Fri, Feb 11, 2011 at 10:49 AM, Michael Moore < >>>>>> michaeljmo...@gmail.com> wrote: >>>>>> >>>>>>> SELECT SUBSTR(MAX(TO_CHAR(p_order, '00000009') || action_flag), 10) >>>>>>> action_flag >>>>>>> FROM participants >>>>>>> >>>>>>> WHERE role_ind BETWEEN 1 AND 20 >>>>>>> AND rpt_num = 311118; >>>>>>> >>>>>>> Mike >>>>>>> >>>>>>> On Fri, Feb 11, 2011 at 12:50 AM, Andrej Hopko >>>>>>> <ado.ho...@gmail.com>wrote: >>>>>>> >>>>>>>> Hi, >>>>>>>> study the KEEP clause, it may work something like this: >>>>>>>> >>>>>>>> SELECT MAX(action_flag) KEEP (DENSE_RANK FIRST ORDER BY p_order DESC >>>>>>>> ) >>>>>>>> >>>>>>>> FROM participants >>>>>>>> WHERE rpt_num = 311118 >>>>>>>> AND role_ind BETWEEN 1 AND 20 >>>>>>>> ; >>>>>>>> >>>>>>>> I am assuming those: >>>>>>>> 1. p_order and participant_order you meant as same column (if >>>>>>>> not, it may still work but I haven't thought about this in deep) >>>>>>>> 2. there may be all kinds of mistakes (I didn't run the query, >>>>>>>> just giving you clues) >>>>>>>> 3. note that MAX after select isn't necessary because KEEP clause >>>>>>>> returns only row with highest p_order, but it is necessary due to >>>>>>>> syntax >>>>>>>> limitation (there need to be aggregation function if remember well - I >>>>>>>> used >>>>>>>> this about a year ago) >>>>>>>> >>>>>>>> all you need now is to clean up the select to correctly return >>>>>>>> data >>>>>>>> >>>>>>>> regards >>>>>>>> hoppo >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On 11. 2. 2011 8:34, gayathri Dev wrote: >>>>>>>> >>>>>>>>> Hi All, >>>>>>>>> Could you please suggest a better way of doing this? >>>>>>>>> SELECT action_flag >>>>>>>>> FROM participants >>>>>>>>> WHERE rpt_num = 311118 >>>>>>>>> AND participant_order = (SELECT MAX(p_order) >>>>>>>>> FROM participants >>>>>>>>> WHERE role_ind BETWEEN 1 AND 20 >>>>>>>>> AND rpt_num = 311118); >>>>>>>>> Thanks, >>>>>>>>> ~G >>>>>>>>> -- >>>>>>>>> You received this message because you are subscribed to the Google >>>>>>>>> Groups "Oracle PL/SQL" group. >>>>>>>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>>>>>>>> To unsubscribe from this group, send email to >>>>>>>>> oracle-plsql-unsubscr...@googlegroups.com >>>>>>>>> For more options, visit this group at >>>>>>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> You received this message because you are subscribed to the Google >>>>>>>> Groups "Oracle PL/SQL" group. >>>>>>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>>>>>>> To unsubscribe from this group, send email to >>>>>>>> oracle-plsql-unsubscr...@googlegroups.com >>>>>>>> For more options, visit this group at >>>>>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>>>>>> >>>>>>> >>>>>>> -- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "Oracle PL/SQL" group. >>>>>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>>>>>> To unsubscribe from this group, send email to >>>>>>> oracle-plsql-unsubscr...@googlegroups.com >>>>>>> For more options, visit this group at >>>>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>>>>> >>>>>> >>>>>> -- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "Oracle PL/SQL" group. >>>>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>>>>> To unsubscribe from this group, send email to >>>>>> oracle-plsql-unsubscr...@googlegroups.com >>>>>> For more options, visit this group at >>>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>>>> >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "Oracle PL/SQL" group. >>>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>>>> To unsubscribe from this group, send email to >>>>> oracle-plsql-unsubscr...@googlegroups.com >>>>> For more options, visit this group at >>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>>> >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Oracle PL/SQL" group. >>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>>> To unsubscribe from this group, send email to >>>> oracle-plsql-unsubscr...@googlegroups.com >>>> For more options, visit this group at >>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Oracle PL/SQL" group. >>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>> To unsubscribe from this group, send email to >>> oracle-plsql-unsubscr...@googlegroups.com >>> For more options, visit this group at >>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>> >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to Oracle-PLSQL@googlegroups.com >> To unsubscribe from this group, send email to >> oracle-plsql-unsubscr...@googlegroups.com >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
