werner.broser said
Nice method, but works only for unique fields!
-- I agree.
When the order-expression does not bear an unique value, "The Holy Grail" can make mistakes:
DECLARE @t1 TABLE ([x] INT, [y] INT)
INSERT INTO @t1 ([x], [y])
SELECT 1, 1
UNION ALL
SELECT 1, 2
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [x] ASC)
+ ROW_NUMBER() OVER (ORDER BY [x] DESC)
- 1 AS [Sum]
FROM @t1) t
The query result gives
x y Sum
1 1 1
1 2 3
which obviously cannot satisfy us.
For rows over which order-expression gives the same value, their relative order will not change regardless of "ASC" or "DESC" option, but only following the order how they are physiclely stored. The previous example proves this.
We should know this limit and do not apply "The Holy Grail" in the wrong place.