integer indices in 'over' clause of row_number() function in S

Grisha wrote:
> Hello David,
> Thank you for the quick answer.
> Well, I don't know about changes in SQL standard, but I still think that t
he
> fact that column numbers are supported in 'regular' order by clause of a
> query but not inside over clause is ...hmmm... strange.
> Also I disagree with you about your comparison of column numbering to
> 'SELECT *'. Indeed, 'SELECT *' could lead to errors when table structure i
s
> changed, but column numbers are something that belongs to specific query,
so
> I think that it's perfectly well to use them.
> I considered to use CTE but since my queries are actually generated
> dynamically from the application it requires a lot of re-coding and
> potentially a lot of new bugs...
> Anyway, actually I wanted to number the records for identifying them later
> on. So I discovered by now that I can 'neutralize' the over clause by
> changing the query
> to:
> select CurrencyCode, row_number() over(order by user) from Sales.Curren
cy
> order by 1
>
That would be a BAD idea. The ROW_NUMBER() will NOT necessarily match
the ORDER BY. Probably this is especially dangerous if your query is
dynamic. Here's a repro:
CREATE TABLE T1 (x INTEGER NOT NULL, y INTEGER NOT NULL PRIMARY KEY);
INSERT INTO T1 (x,y)
SELECT 1,9 UNION ALL
SELECT 2,8 UNION ALL
SELECT 3,7 UNION ALL
SELECT 4,6 ;
SELECT x,y,
ROW_NUMBER() OVER(ORDER BY user) AS row_num
FROM T1
ORDER BY 1 ;
I get:
x y row_num
-- -- --
1 9 4
2 8 3
3 7 2
4 6 1
(4 row(s) affected)
YMMV

> Anyway, IMHO this is just bug...
> Thanks again,
> Grisha
>
It is certainly an error in the documentation because the help on the
OVER (ORDER BY ...) clause specifies that column aliases may be used:
http://msdn2.microsoft.com/ms188385.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--Hello David,
thank you for the example. I know that it will not match query's ORDER BY.
But it will still assign unique numbers to the records (although not in any
particular order) and this is enough for me.
Of course, it doesn't reflect the idea for which row_number() function was
designed, but I don't care. Probably MS programmers should care for it... :-
)
"David Portas" wrote:

> Grisha wrote:
> That would be a BAD idea. The ROW_NUMBER() will NOT necessarily match
> the ORDER BY. Probably this is especially dangerous if your query is
> dynamic. Here's a repro:
> CREATE TABLE T1 (x INTEGER NOT NULL, y INTEGER NOT NULL PRIMARY KEY);
> INSERT INTO T1 (x,y)
> SELECT 1,9 UNION ALL
> SELECT 2,8 UNION ALL
> SELECT 3,7 UNION ALL
> SELECT 4,6 ;
> SELECT x,y,
> ROW_NUMBER() OVER(ORDER BY user) AS row_num
> FROM T1
> ORDER BY 1 ;
> I get:
> x y row_num
> -- -- --
> 1 9 4
> 2 8 3
> 3 7 2
> 4 6 1
> (4 row(s) affected)
> YMMV
>
> It is certainly an error in the documentation because the help on the
> OVER (ORDER BY ...) clause specifies that column aliases may be used:
> http://msdn2.microsoft.com/ms188385.aspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>