SQL SERVER – 2005 – Find Nth Highest Record from Table – Using CTE

This question is quite a popular question and it is frequently asked question in interview that retrieve Nth highest record from table you can use top keyword and distinct and temp table. But, it will degrade your performance so in practice, you can easily use New features of SQL Server 2005 and that is CTE. Let us see....

USE AdventureWorks
GO


WITH SALCTE

AS

(

      SELECT e1.*,

      Row_number() OVER(ORDER BY e1.Rate DESC) AS Rank
      FROM HumanResources.EmployeePayHistory AS e1

)
SELECT * FROM SALCTE

WHERE Rank = 4

It will give you 4th highest record. Suppose if you want 5th, 6th or nth highest record then write 5th, 6th or nth instead of Rank = 4.