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
--

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
> --
>

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

>> Well, I don't know about changes in SQL standard, but I still think that
the fact that column numbers are supported in 'regular' order by clause of a
query but not inside over clause is ...hmmm... strange. <<
Sloppy is probably a better word :) They are not supposed to be
supported in any ORDER BY clause. The goal of X3 was to get rid of all
position number column references after SQL-92, so that you do not
confuse an attribute with an expression, or depend on the current state
of a table at runtime .
The only places that you get implicit column names is in the expansion
of SELECT * and missing column lists in INSERT INTO. There was even
talk of gettign rid of those things.On 18 Jan 2006 06:20:11 -0800, --CELKO-- wrote:

>Sloppy is probably a better word :) They are not supposed to be
>supported in any ORDER BY clause. The goal of X3 was to get rid of all
>position number column references after SQL-92, so that you do not
>confuse an attribute with an expression, or depend on the current state
>of a table at runtime .
Hi Joe,
But unlike a standards committe, MS SQL Server has an established user
base with billions of line of deployed code. They can't just remove a
syntax element that was valid in the previous release.
BTW, given that SELECT * should never be used in a query, how does ORDER
BY ordinal_position rely on the current state of a table at runtime?

>The only places that you get implicit column names is in the expansion
>of SELECT * and missing column lists in INSERT INTO. There was even
>talk of gettign rid of those things.
Too bad that it was only talk.
Hugo Kornelis, SQL Server MVP