I have previously written SQL SERVER – 2005 – Find Nth Highest Record from Database Table where I have shown query to find 4th highest record from database table. Everytime when I write blog I am always very eager to read comments of readers. Some of regular readers are industry leaders and and their comments always teach us all something new. One of them is Nicholas Paldino [.NET/C# MVP]. He has always provided valuable solution and comments to this blog. His recent comment about finding Nth Highest Record is quite an interesting.
row_number() OVER (
ORDER BY e1.Rate DESC) AS _Rank
HumanResources.EmployeePayHistory AS e1
) AS t
t._Rank = 4
Reference : Pinal Dave (http://blog.SQLAuthority.com) , Nicholas Paldino [.NET/C# MVP]