Thanks again,

I have manged to find a solution as you said I used rank in instead of row_number but also needed to add distinct on the 2nd select. otherwise...

Wow what a genius way to solve this. Was very...