the WITH part just simulates the input table.
WITH myinp
AS (SELECT 2 ord, 'Y' flg FROM DUAL
UNION ALL
SELECT 4 ord, 'N' flg FROM DUAL
UNION ALL
SELECT 6 ord, 'Y' flg FROM DUAL
UNION ALL
SELECT 7 ord, 'N' flg FROM DUAL
UNION ALL
SELECT 8 ord, 'N' flg FROM DUAL)
SELECT CASE
WHEN (SELECT flg
FROM myinp r
WHERE NOT EXISTS
(SELECT 1
FROM myinp q
WHERE q.ord > r.ord)) = 'Y'
THEN
(SELECT TO_CHAR(ord)
FROM myinp w
WHERE flg = 'N'
AND NOT EXISTS
(SELECT 1
FROM myinp x
WHERE flg = 'N' AND w.ord > x.ord))
ELSE
(SELECT TO_CHAR(ord)
FROM myinp mm
WHERE ord > (SELECT ord
FROM myinp w
WHERE flg = 'Y'
AND NOT EXISTS
(SELECT 1
FROM myinp x
WHERE flg = 'Y' AND w.ord <
x.ord))
AND NOT EXISTS
(SELECT 1
FROM myinp g
WHERE ord > (SELECT ord
FROM myinp w
WHERE flg = 'Y'
AND NOT EXISTS
(SELECT 1
FROM myinp x
WHERE flg = 'Y'
AND w.ord < x.ord))
AND mm.ord > g.ord))
END
last_flg
FROM DUAL;
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
>
--
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
