RE: rewrite self joins using analytic functions

  • From: "Powell, Mark" <mark.powell2@xxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 18 Mar 2010 14:11:48 +0000

I often cannot see the obvious but I do not see where any of the analytic 
functions would be applicable to this query.

Are the where clause conditions where tab1.col1 and tab1.col2 are both tsted 
equal againt tab2.col2 correct?  Possible and not really material to answering 
your question but this caught my eye and I can't get past it.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of hrishy
Sent: Thursday, March 18, 2010 5:11 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: rewrite self joins using analytic functions


Any ideas on how to re-write this self join using Analytical functions

tab2.col1 ,
WHERE tab1.col1      = tab2.col2
AND   tab1.col2  =   tab2.col2
AND   tab1.col4         = tab2.col3
AND   tab2.col3     < tab1.col3
AND tab2.col5         = 3
AND tab1.col5          = 4
AND tab2.col6 NOT IN(303,321)
AND tab1.col6     not in (322)
GROUP BY tab2.col1,tab2.col2

Other related posts: