Thanks Mike.. this works...:-)
On Fri, Jun 17, 2011 at 11:54 AM, Michael Moore <michaeljmo...@gmail.com>wrote: > I have tested this, and I am pretty sure it works correctly according to > our requirement. > > WITH is1234 > AS (SELECT tab1.col2 tab1col2, > tab1.col3 tab1col3, > tab2.col1 tab2col1, > tab2.col2 tab2col2, > tab2.col3 tab2col3 > FROM tab1 JOIN tab2 ON tab2.col2 = tab1.col2 > WHERE tab2.col1 = 1234), > isnot1234 > AS (SELECT tab1.col2 tab1col2, > tab1.col3 tab1col3, > tab2.col1 tab2col1, > tab2.col2 tab2col2, > tab2.col3 tab2col3 > FROM tab1 JOIN tab2 ON tab2.col2 = tab1.col2 > WHERE tab2.col1 <> 1234) > SELECT CASE > WHEN (SELECT COUNT (*) FROM isnot1234) > 0 > THEN > (SELECT tab1col3 > FROM isnot1234 o > WHERE NOT EXISTS > (SELECT 1 > FROM isnot1234 i > WHERE i.tab2col3 > o.tab2col3)) > ELSE > (SELECT tab1col3 > FROM is1234 o > WHERE NOT EXISTS > (SELECT 1 > FROM is1234 i > WHERE i.tab2col3 < o.tab2col3)) > END > ans > FROM DUAL; > > On Fri, Jun 17, 2011 at 9:31 AM, rich p <richg...@gmail.com> wrote: > >> There are two analytic functions you might want to look into: first_value >> or last_value. It depends on how you look at the query problem at hand. You >> can get by using just one because if you think about it, the "FIRST_VALUE" >> of an ordered series sorted in DESCENDING will also be the "LAST_VALUE" of >> that same series when sorted in ASCENDING order. >> >> You can verify this using your test table and the following query sample: >> >> select tab1.col3, tab2.col3, >> first_value ( tab1.col3 ) over ( order by tab2.col3 desc ) as test_1, >> first_value ( tab1.col3 ) over ( order by tab2.col3 asc ) as test_2 >> from tab1, tab2 >> where tab2.col2 = tab1.col2 >> >> One thing you will have to consider however is that the set you order by >> in descending order is further constrained in your "where" clause by the >> value in tab2.col1 ( = 1234 or <> 1234 ) so the set you will be manipulating >> using with each analytic function call will be a different and mutually >> exclusive subset of the above query. >> >> Rich Pascual >> >> -- >> 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
