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 Hi Any ideas on how to re-write this self join using Analytical functions SELECT tab2.col1 , tab2.col2 sum(tab2.col3) count(*) FROM tab1,tab2 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