I just realised that the order will be in sequence. that made it better...
this is what i have tried.. but still i am sure.. there can be a better
one.. please suggest.
select
case when MAX_ORD = MAX_ACTION_TAKEN then MIN_ACTION_NOT_TAKEN
when MAX_ORD <> MAX_ACTION_TAKEN then MAX_ACTION_TAKEN + 1
when MAX_ACTION_TAKEN is null and NOT_TAKEN_ACTION > 0 then
MIN_ACTION_NOT_TAKEN end as next_ord
from (
select
max(ORDER) max_ord,
max(case when ACTION_flag = 'Y' then ORDER end) MAX_ACTION_TAKEN,
SUM(case when ACTION_flag is null then 1 else 0 end)
NOT_TAKEN_ACTION,
min(case when ACTION_flag is null then ORDER end)
MIN_ACTION_NOT_TAKEN
FROM participants
order by ORDER);
On Thu, Feb 17, 2011 at 2:08 PM, gayathri Dev <[email protected]> wrote:
> Hi Avinash,
>
> it should pick 2
>
> thanks
>
> On Thu, Feb 17, 2011 at 1:56 PM, Avinash Naidu <[email protected]>wrote:
>
>> order action_flag
>> 2 N
>> 4 Y
>> 6 N
>> 7 N
>> 8 Y
>> should pick 2 or 6??
>>
>> On Thu, Feb 17, 2011 at 1:31 PM, gayathri Dev <[email protected]> wrote:
>>
>>> Hi all,
>>>
>>> I am seriously thinking I need a good teacher who can teach me analytical
>>> functions.
>>> But with a min time i have got to resolve the hurdle I have.
>>> Below are the examples..
>>> basically I shld check the last order's action flag if it is Y, then I
>>> shld pick the min order whose action_flag is N
>>> else if the last order's action flag is N, then I shld pick the greatest
>>> order who has taken action(action_flag = 'Y')
>>> and choose the next order after him.
>>>
>>> order action_flag
>>> 2 Y
>>> 4 N
>>> 6 N
>>> 7 N
>>> 8 Y
>>> should pick 4
>>>
>>> order action_flag
>>> 2 Y
>>> 4 N
>>> 6 Y
>>> 7 N
>>> 8 N
>>> should select 7
>>>
>>> order action_flag
>>> 2 N
>>> 4 N
>>> 6 N
>>> 7 N
>>> 8 N
>>> should select 2
>>>
>>> order action_flag
>>> 2 N
>>> 4 Y
>>> 6 N
>>> 7 N
>>> 8 N
>>> 12 Y
>>> 14 N
>>> 15 N
>>> should select 14
>>>
>>> Please help me building this query.
>>>
>>> Thanks in advance
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Oracle PL/SQL" group.
>>> To post to this group, send email to [email protected]
>>> To unsubscribe from this group, send email to
>>> [email protected]
>>> 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 [email protected]
>> To unsubscribe from this group, send email to
>> [email protected]
>> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
