Eddited, because I missed part of requirements. This should perform better than following solution.

;with a as(

Select

ROW_NUMBER() OVER ( Partition by...