Hi TRY THIS... select case when ACTION_FLAG = 'Y' then (select min(ORDER_NO) from ABC where ACTION_FLAG = 'N') when ACTION_FLAG = 'N' then NVL((select min(ORDER_NO) from ABC where ORDER_NO > (select max(ORDER_NO) from ABC where ACTION_FLAG = 'Y')), (SELECT MIN(ORDER_NO) FROM ABC )) end acc from ABC where order_no = (select max(order_no) from ABC);
On Fri, Feb 18, 2011 at 12:31 AM, 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
