Yes Rich! you are right.. p_order and participant_order are same col.. that was a typo.
On Fri, Feb 11, 2011 at 11:06 AM, Richard Pascual <richg...@gmail.com>wrote: > Are the columns, p_order and participant_order different columns? > > Rich Pascual > > > > On Fri, Feb 11, 2011 at 10:49 AM, Michael Moore > <michaeljmo...@gmail.com>wrote: > >> 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 <ado.ho...@gmail.com>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 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 >> > > -- > 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
