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