Row_Number function in SQL Server 2005


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.

Advertisements

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s