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
