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 : 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
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.
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
Assigning row number without any order
The general answer to the question of a row_number without an ordering is to order over a constant - row_number() over (order by 1)
In your case the expected output shows that the row number is actually a rank based upon the col b values, so what you actually want is - dense_rank() over (order by COLB)
Related Topics
Query to Find Nᵗʰ Max Value of a Column
How to Insert Null Values into SQL Server
How to Reset an MySQL Autoincrement Using a Max Value from Another Table
SQL Select Rows with Only a Certain Value in Them
How to Create a Conditional Where Clause
MySQL Get Table Column Names in Alphabetical Order
How to Treat Max() of an Empty Table as 0 Instead of Null
SQL How to Convert Row with Date Range to Many Rows with Each Date
SQL Server Equivalent of MySQL's Now()
Rails Includes with Conditions
Using Input from a Text File for Where Clause
How to Pull a List of Id's from a SQL Table as a Comma-Separated Values String
Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns
Removing Leading Zeros from Varchar SQL Developer
What Is the Purpose of Putting an 'N' in Front of Function Parameters in Tsql