sorry Mike.. didnt realised the new message.. going thr your response now. On Thu, Feb 17, 2011 at 3:41 PM, gayathri Dev <gd0...@gmail.com> wrote:
> 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 <gd0...@gmail.com> wrote: > >> Hi Avinash, >> >> it should pick 2 >> >> thanks >> >> On Thu, Feb 17, 2011 at 1:56 PM, Avinash Naidu <avisna...@gmail.com>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 <gd0...@gmail.com>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 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
