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 :
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 :
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:
Tags: Sql Server 2005
4 Comments »
Leave a comment
About Jeff
Hi, my name is Jeff, and I’ve been happily married since 1997 to my beautiful wife Dana. We have four wonderful children, and live in Clarion County PA. I am an ASP.NET Developer for an incredible company called Millenium Pharmacy Systems, in Cranberry PA. I am also an outdoorsman, avid reader and video game player, and a big time Philadelphia sports fan. If you’re a fan as well, then you know the shear joy the Phillies brought to your life this past October !
Blogger Jeff = new Blogger 
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?
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
How can I convert,
SELECT ROW_NUMBER() OVER(ORDER BY HireDate DESC,LastName DESC) AS ‘RowNumber’
,LastName
,HireDate
FROM dbo.Employees
into LINQ?
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!!!!