Home >Database >Mysql Tutorial >How Can I Use ROW_NUMBER() Without ORDER BY in SQL Server 2005?
ROW_NUMBER() without ORDER BY in SQL Server 2005
In SQL Server 2005, the ROW_NUMBER() function can be used to generate a unique sequential number for each row in a result set. However, unlike its counterparts in later versions, ROW_NUMBER() in SQL Server 2005 requires an explicit ORDER BY clause. This limitation poses a challenge when attempting to generate sequential numbers without altering the original order of the data.
Solution:
To work around this limitation, we can avoid specifying an explicit ordering using the following query syntax:
INSERT INTO TargetTable (ID, FIELD) SELECT Row_Number() OVER (ORDER BY (SELECT 1)) + Coalesce( (SELECT Max(ID) FROM TargetTable WITH (TABLOCKX, HOLDLOCK)), 0 ), FieldValue FROM SourceTable WHERE {somecondition};
Explanation:
Considerations:
The above is the detailed content of How Can I Use ROW_NUMBER() Without ORDER BY in SQL Server 2005?. For more information, please follow other related articles on the PHP Chinese website!