RE: rewrite self joins using analytic functions
- From: hrishy <hrishys@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, mark.powell2@xxxxxx
- Date: Fri, 19 Mar 2010 00:45:35 +0000 (GMT)
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
Other related posts:
