SQL Tips by Namwar Rizvi

May 26, 2007

Finding Nth Highest value — Wonders of Row_Number()

Filed under: New Features,Row_Number,SQL Server 2005,tips,TSQL — namwar @ 12:33 AM

Finding Nth highest value is one of the most common requirements of Reports. For example, you may have a requirement of finding 5th Highest salaried employee or 3rd most expensive item etc. In SQL Server 2005, it is now very easy to find Nth item in the result because of the Row_Number function. Using Row_Number is also very efficient and does not impact your system performance. Following is the code to demonstrate the usage of Row_Number() function to find 5th highest value. You can replace 5 by any number to find Nth highest value.

–Disable intermediate SQL Server messages
Set NoCount On

—Create a table variable
Declare @m_TestTable table ( id int, value int)

–Insert 100 Random values
Declare @m_Index int
Select @m_Index=1
While @m_Index <101
Begin
Insert into @m_TestTable values (@m_Index,Ceiling(10000 * Rand()))
Select @m_Index=@m_Index+1

End

—Return 5th highest
Select id,Value from
(
Select id,value ,(Row_Number() Over (Order by value desc)) rowNum
from @m_TestTable

) A
Where A.rowNum=5

Advertisements

1 Comment »

  1. good one

    Comment by shailendra — July 31, 2008 @ 12:48 PM | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: