Calvin_Qiu,Joseph and eeswar thanks for the response..

Actually I was working on a querry which have lots of subquerries and unions..The problem I stated was just a part of that...

I have tried all the options submitted by you guys..

In my case Josephs soln seem to give a better performance..

And calvin the second query submitted by you seems to not work in Oracle...however I am very curious to work on it..

Thanks for all the help extended,

Regards,

Paddy