Blogger Jeff = new Blogger

Programming and stuff in Western PA

Sql Server 2005 ROW_NUMBER function

The ROW_NUMBER function is a neat little enhancement that makes a fairly common task in Sql pretty easy : generating a row number for a result set. You’ve always been able to do this Sql, but had to do something like the following :

CREATE TABLE #temp
(
	RowNumber int identity
	,LastName varchar(100)
	,HireDate datetime
)

INSERT INTO #temp(LastName
	,HireDate)
SELECT LastName
	,HireDate
FROM dbo.Employees
ORDER BY HireDate DESC,LastName DESC

SELECT * FROM #temp

Results :

Result Set 1

Why would you want to return a unique row number in a result set? A lot of times it’s for more complex comparisons, or maybe for a paging function.

The ROW_NUMBER function makes this step easier :

SELECT ROW_NUMBER() OVER(ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate
FROM dbo.Employees

Results :

Result Set 2

All we’re basically doing here is telling the result set how the RowNumber will be assigned. This is doing the same thing as our first example.

However, the ROW_NUMBER function has a PARTITION attribute we can set. What this does is first group our data by one or more columns, and then assign the RowNumber column. For instance in our example above, we could partition the data on TitleOfCourtesy(Mr.,Mrs.,etc). What this means is for each unique instance of TitleOfCourtesy, we assign a RowNumber. So our Sql looks like this :

SELECT ROW_NUMBER() OVER(PARTITION BY TitleOfCourtesy 
			ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate,TitleOfCourtesy
FROM dbo.Employees
ORDER BY ROWNUMBER

Which gives us this result set:

Result Set 3

Tags:

Advertisements

January 25, 2008 - Posted by | SQL Server, Technology

4 Comments »

  1. In addition to using server side custom paging in a stored procedure, I also need the ability to pass in a custom sort expression.

    Someone posted a similar question here:

    http://bytes.com/groups/net-asp/659588-sending-sortexpression-sql-command-parameter

    I could do something like: @sql = “select row_number() over (order by ‘+ @sort_expression +’)’……..
    but this is not maintainable and not efficient.

    Any ideas?

    Comment by sqluser | January 22, 2009 | Reply

  2. There’s a couple of things you can do:

    (1)Take a sort param and use it in a case statement in you sql :
    declare @opt int ; set @opt=2
    SELECT ROW_NUMBER() OVER(ORDER BY case when @opt=1 then ModifiedDate else BirthDate end DESC) AS ‘RowNumber’
    ,Title,ModifiedDate
    FROM HumanResources.Employee

    (2)Take the sort parameter and use it with sp_executesql. This would be effeicient

    Comment by rudesyle | January 22, 2009 | Reply

  3. How can I convert,

    SELECT ROW_NUMBER() OVER(ORDER BY HireDate DESC,LastName DESC) AS ‘RowNumber’
    ,LastName
    ,HireDate
    FROM dbo.Employees

    into LINQ?

    Comment by John Gathogo | July 1, 2009 | Reply

  4. I am trying to find a way to retrieve only 2 most recent orders for each vendor regardless of how many they have, they may also have only 1 order. I cant seem to get it right.

    Can you please suggest how to code it in SQL? I was thinking of using ROW_NUMBER() OVER but I cannot seem to get the correct results.
    Help please!!!!

    Comment by Monika | July 7, 2010 | Reply


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: