RE: rewrite self joins using analytic functions

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Fri, 19 Mar 2010 00:45:35 +0000 (GMT)
Message-ID: <844931.71516.qm_at_web29009.mail.ird.yahoo.com>

Hi Mark/Connor
 

Apologies while trying to obfuscate the table names i ended up giving two table names instead of one.Here is the corrected query
 

Hi
 

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

SELECT
t2.col1 ,
t2.col2
sum(t2.col3)
count(*)
FROM
tab1   t1 ,
tab1   t2

WHERE t1.col1      = t2.col1
AND   t1.col2  =   t2.col2
AND   t1.col4         = t2.col4
AND   t1.col3     < t2.col3
AND t1.col5         = 3
AND t2.col5          = 4

AND t1.col6 NOT IN(303,321)
AND t2.col6     not in (322)
GROUP BY t2.col1,t2.col2

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 18 2010 - 19:45:35 CDT