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

Hello,
I tried to use the new row_number() function while adjusting my application
to SQL 2005. For example, this query:
--
use AdventureWorks
select CurrencyCode, row_number() over(order by 1) from Sales.Currency
order by 1
--
worked perfectly in the CTP release (9.00.1187.00). Strangely,
when I installed the application on the final release of SQL 2005
(9.00.1399.00), I got the following error message:
Msg 5308, Level 16, State 1, Line 2
Windowed functions do not support integer indices as ORDER BY clause
expressions.
If I use the column name instead of the number (i.e., 'order by
CurrencyCode'), the query works.
The problem is that in my real queries the fields in select clause are much
more complicated and contain UDFs, 'case' clauses etc., so it doesn't seem
right to repeat the whole expression in the over clause. I also can't use
aliases for the number of reasons.
So the question is:
Does anybody know why this feature was removed from the final release of
SQL2005? Is there any chance that it'll be fixed?
Is there any idea how can I use the integer numbers to designate the fields
in over(order by) clause?
Thanks in advance,
GrishaGrisha wrote:
> Hello,
> I tried to use the new row_number() function while adjusting my applicatio
n
> to SQL 2005. For example, this query:
> --
> use AdventureWorks
> select CurrencyCode, row_number() over(order by 1) from Sales.Currency
> order by 1
> --
> worked perfectly in the CTP release (9.00.1187.00). Strangely,
> when I installed the application on the final release of SQL 2005
> (9.00.1399.00), I got the following error message:
> Msg 5308, Level 16, State 1, Line 2
> Windowed functions do not support integer indices as ORDER BY clause
> expressions.
> If I use the column name instead of the number (i.e., 'order by
> CurrencyCode'), the query works.
> The problem is that in my real queries the fields in select clause are muc
h
> more complicated and contain UDFs, 'case' clauses etc., so it doesn't seem
> right to repeat the whole expression in the over clause. I also can't use
> aliases for the number of reasons.
> So the question is:
> Does anybody know why this feature was removed from the final release of
> SQL2005? Is there any chance that it'll be fixed?
> Is there any idea how can I use the integer numbers to designate the field
s
> in over(order by) clause?
> Thanks in advance,
> Grisha
I'm not certain, but probably MS followed the SQL standard, which I
think no longer supports column index numbers. Someone may correct me
on that point though as I don't have the docs in front of me.
In any case, I think this is a good thing (not allowing column numbers
I mean). Using column numbers should be avoided for the same reasons we
avoid "SELECT *" - to reduce maintenance costs and potential for errors
when the table structure is modified.
What I don't understand is why you can't use an alias. How about
putting your query into a CTE?
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
--