Pages

Tuesday, May 11, 2010

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. 

0 comments:

Post a Comment