We would like to see the efficient way Mike. At least, what I believe is first solve the problem, then look for efficiency. Now, as problem has been solved, We can look for efficiency.
Regards, On Sat, Jun 18, 2011 at 8:09 PM, Michael Moore <[email protected]>wrote: > 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 < > [email protected]> 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" <[email protected]> wrote: >> > >> > Thanks Mike.. this works...:-) >> > >> > >> > >> > On Fri, Jun 17, 2011 at 11:54 AM, Michael Moore < >> [email protected]> 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 <[email protected]> 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 [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 >> > > -- > 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
