It's probably not the most efficient way to go about it and if it was a large table, I'd probably do it a different way. The one advantage it might have is for maintenance. I.E, the logic of what you are trying to do is clearly exposed by the structure of the statement.
Mike On Fri, Jun 17, 2011 at 8:47 PM, Jignesh Makwana <makwanajigne...@gmail.com>wrote: > G, > > You can use materialize hint in with clause to cache the result of the > query. > > regards, > > On 18 Jun 2011 03:58, "Gayathri" <gd0...@gmail.com> wrote: > > > > 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 > > -- > 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
