ROW_NUMBER Without ORDER BY
There is no need to worry about specifying constant in the ORDER BY
expression. The following is quoted from the Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions written by Itzik Ben-Gan
(it was available for free download from Microsoft free e-books site):
As mentioned, a window order clause is mandatory, and SQL Server
doesn’t allow the ordering to be based on a constant—for example,
ORDER BY NULL. But surprisingly, when passing an expression based on a
subquery that returns a constant—for example, ORDER BY (SELECT
NULL)—SQL Server will accept it. At the same time, the optimizer
un-nests, or expands, the expression and realizes that the ordering is
the same for all rows. Therefore, it removes the ordering requirement
from the input data. Here’s a complete query demonstrating this
technique:
SELECT actid, tranid, val,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM dbo.Transactions;
Observe in the properties of the Index Scan iterator that the Ordered
property is False, meaning that the iterator is not required to return
the data in index key order
The above means that when you are using constant ordering is not performed. I will strongly recommend to read the book as Itzik Ben-Gan
describes in depth how the window functions are working and how to optimize various of cases when they are used.
SQL Server 2005 ROW_NUMBER() without ORDER BY
You can avoid specifying an explicit ordering as follows:
INSERT dbo.TargetTable (ID, FIELD)
SELECT
Row_Number() OVER (ORDER BY (SELECT 1))
+ Coalesce(
(SELECT Max(ID) FROM dbo.TargetTable WITH (TABLOCKX, HOLDLOCK)),
0
),
FieldValue
FROM dbo.SourceTable
WHERE {somecondition};
However, please note that is merely a way to avoid specifying an ordering and does NOT guarantee that any original data ordering will be preserved. There are other factors that can cause the result to be ordered, such as an ORDER BY
in the outer query. To fully understand this, one must realize that the concept "not ordered (in a particular way)" is not the same as "retaining original order" (which IS ordered in a particular way!). I believe that from a pure relational database perspective, the latter concept does not exist, by definition (though there may be database implementations that violate this, SQL Server is not one of them).
The reason for calculating the Max
in the query and for adding the lock hints is to prevent errors due to a concurrent process inserting using the same value you plan to use, in between the parts of the query executing. The only other semi-reasonable workaround would be to perform the Max() and INSERT in a loop some number of times until it succeeds (still far from an ideal solution). Using an identity column is far superior. It's not good for concurrency to exclusively lock entire tables, and that is an understatement.
Note: Many people use (SELECT NULL)
to get around the "no constants allowed in the ORDER BY clause of a windowing function" restriction. For some reason, I prefer 1
over NULL
. What you use is up to you.
SQL Server : Row Number without ordering
Using ORDER BY (SELECT NULL) will give you the results your looking for.
SELECT
VariableName,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM
SeanVault.dbo.TempVarIDs
need to use ROW_NUMBER without over in sqlserver 2005
Use several ROW_NUMBER clauses per column and choose the relevant one:
....
ROW_NUMBER() OVER (ORDER BY [Column1]) as rn1,
ROW_NUMBER() OVER (ORDER BY [Column2]) as rn2,
ROW_NUMBER() OVER (ORDER BY [Column3]) as rn3,
ROW_NUMBER() OVER (ORDER BY [Column4]) as rn4
....
OR use a CASE expression. Note: all datatypes must be compatible
ROW_NUMBER() OVER (ORDER BY CASE @sort
WHEN 1 THEN [Column1]
WHEN 1 THEN [Column2]
WHEN 1 THEN [Column3]
...
END
OR If you really want an arbitrary row number then do this:
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rn
oracle rownum transfer to t-sql row_number() over with error
The ORDER BY
clause in the subquery appears to be the problem, and SQL Server is telling you that ORDER BY
cannot appear in a subquery without also using something like TOP
. However, it should not even be necessary here, because your call to ROW_NUMBER
already specifies an ORDER BY
clause to be used when assigning row numbers. Try this version:
SELECT t.*, staff_no + '-' + t.rownum AS pk
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY staff_no, postingdate) AS rownum, *
FROM posting
WHERE type NOT IN ('X', 'C')
) t;
Related Topics
How to Sort a 'Version Number' Column Generically Using a SQL Server Query
Delete Parent If It's Not Referenced by Any Other Child
SQL Error "Ora-01722: Invalid Number"
SQL Error: Ora-00933: SQL Command Not Properly Ended
Why Can't I Use Column Aliases in the Next Select Expression
Pivot Query on Distinct Records
Where in Condition Not Accepting String Value
SQL Keep Getting Error with on Update Cascade
Normalization in Plain English
How to Get Other Columns When Using Spark Dataframe Groupby
Is There Auto Increment in SQLite
SQL Script to Find Invalid Email Addresses
Name Database Design Notation You Prefer and Why