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() OVER(PARTITION BY Number%10 ORDER BY Number) AS AlternateNTile

FROM RandomData

ORDER BY 1

OPTION (MAXRECURSION 200)