RSS

Using Microsoft SQL Server ROW_NUMBER() function without ordering data

13 Jun

For TSQL developers ROW_NUMBER() is a nice, smart way to generate incrementing id for result set for set based TSQL queries.Consider following query.

SELECT ROW_NUMBER( ) OVER( ORDER BY ContactName), 
ContactName,ContactTitle  
FROM dbo.Customers

As you can see you need to order by any one of column names to get the ROW_NUMBER( ) function to work.
However sometimes you might need not to get result set by ordering by a column how ever without order by clause up to SQL server 2008 do not allow to use the ROW_NUMBER( ) function.

What is the solution? Solution to this is as following

SELECT ROW_NUMBER( ) OVER( ORDER BY (SELECT NULL) ), ContactName,ContactTitle  FROM dbo.Customers 

Passing sub query that return null will do the trick!….. this way row id is given to the rows in arbitrary order rather than ordering by a column in the result set.

Advertisements
 
Leave a comment

Posted by on June 13, 2011 in SQL

 

Leave a Reply

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

 
%d bloggers like this: