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. 

SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 5th highest salary from Employee table ,

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 5 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

If you want to retrieve a nth Maximum value then use nth value instead of 5 and enjoy!!!!!!!

Cheers