Window Functions are a great addition to TSQL. I use them all the time now.

WITH RandomData AS

(

SELECT 1 AS Number

UNION ALL

SELECT Number + 1

FROM RandomData

WHERE Number < 200

)

SELECT

Number,

ROW_NUMBER()...