SELECT SUBSTR(MAX(TO_CHAR(p_order, '00000009') || action_flag), 10)
action_flag
FROM participants
WHERE role_ind BETWEEN 1 AND 20
AND rpt_num = 311118;
Mike
On Fri, Feb 11, 2011 at 12:50 AM, Andrej Hopko <[email protected]> wrote:
> Hi,
> study the KEEP clause, it may work something like this:
>
> SELECT MAX(action_flag) KEEP (DENSE_RANK FIRST ORDER BY p_order DESC )
>
> FROM participants
> WHERE rpt_num = 311118
> AND role_ind BETWEEN 1 AND 20
> ;
>
> I am assuming those:
> 1. p_order and participant_order you meant as same column (if not, it
> may still work but I haven't thought about this in deep)
> 2. there may be all kinds of mistakes (I didn't run the query, just
> giving you clues)
> 3. note that MAX after select isn't necessary because KEEP clause
> returns only row with highest p_order, but it is necessary due to syntax
> limitation (there need to be aggregation function if remember well - I used
> this about a year ago)
>
> all you need now is to clean up the select to correctly return data
>
> regards
> hoppo
>
>
>
> On 11. 2. 2011 8:34, gayathri Dev wrote:
>
>> Hi All,
>> Could you please suggest a better way of doing this?
>> SELECT action_flag
>> FROM participants
>> WHERE rpt_num = 311118
>> AND participant_order = (SELECT MAX(p_order)
>> FROM participants
>> WHERE role_ind BETWEEN 1 AND 20
>> AND rpt_num = 311118);
>> Thanks,
>> ~G
>> --
>> 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
