Thought I'd throw in my $.02
Created a table test_sql (ord number, flg varchar2(1)); inserted
test records as below. This seems to work (though perhaps I am over
simplifying):
SELECT CASE WHEN max_flg = 'N'
THEN (select min(ord)
from test_sql
where flg = 'N'
and ord > NVL((select max(ord)
from test_sql
where flg = 'Y'),0) )
ELSE
(select min(ord)
from test_sql
where flg = 'N')
END selected_ord
FROM (select flg max_flg
from test_sql
where ord = (select max(ord) from test_sql))
On Feb 17, 7:43 pm, gayathri Dev <gd0...@gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
--
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
