This article explains about the Row_Number function in SQL Server 2005. It explains whether SQL Server 2005 Row_Number function will support SubQueries or not.
Introduction
SQL Server 2005 have introduced new function ROW_NUMBER to find the Row number of a particular column if its specified in orderby Clause
The syntax for RowNumber is,
SELECT ROW_NUMBER () OVER (ORDER BY Column Name) AS Rowid FROM tablename
Where Rowid will retrieve the row number based on the columns.
Example:
SELECT ROW_NUMBER () OVER (ORDER BY names) AS Rowid, names ,id
FROM venkitable
Usage of Row_Number:
I have tried for an R&D on row number like removing order by clause. OOPS it’s displaying an error like
the rownumber attribute should have Order by clause by default.
Example:
SELECT ROW_NUMBER () OVER() AS venkatid, names ,id
FROM venkitable
I am thinking like it will take some default column and order my rownumber. But, microsoft is not provide such freedom to me. So, we have to give the order by clause mandatorily.
I didnt stop my search on Rownumber. Now, i am deviated towards using subqueries in oder by clause
I am trying all the combinations of subqueries in orderby clause and had a tough fight to execute my query. But sql server 2005 row_number order by clause is not accepting my offer of using subquery.
Example:
— WILL INDICATE AN ERROR BECAUSE THE SUBQUERY WILL GIVE MORE THAN ONE VALUES
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT ID FROM venkitable)) AS ID,
NAMES FROM venkitable
Summary
Microsoft provides much more flexibility in this version which includes Row_Number to retrieve the rowid instead of tiresome huge queries. Thanks to microsoft.