Find nth highest and lowest salary of an Employee

This question has been asked many times in interview to candidate that find or fetch nth highest and lowest salary of an Employee like 2nd, 3rd, 4th, 5th… highest and lowest salary of an Employee from Employee table. This question some times also asked as get nth highest and lowest salary of an Employee without using TOP and sub query. Let’s see solutions for all this questions in this article.

Suppose we have following table of Employee with Name and Salary of Employee.

Query to get nth(3rd) highest salary of an Employee

Suppose we want to get 3rd highest salary of an Employee so query should be like.

select top 1 Salary as '3rd highest salary' from (select distinct top 3 Salary from Employee order by Salary desc)   a order by Salary asc

Let me illustrate above query as here we want to fetch nth(3rd) highest salary of an Employee so first inside sub query we find all top n(3) distinct records using order by descending which result in to top n(3) records with highest salary and than we fetch top 1 record among all these records with order by ascending which full fill our requirement to find nth(3rd) highest salary of an Employee.

Query to get nth(3rd) lowest salary of an Employee

select top 1 Salary as '3rd lowest salary' from (select distinct top 3 Salary from Employee order by Salary asc)   a order by Salary desc

See here we want to find nth(3rd) lowest salary of an Employee so our logical of fetching data will be reversed so first inside sub query we find all top n(3) distinct records using order by ascending which result in to top n(3) records with lowest salary and than we fetch top 1 record among all these records with order by descending which full fill our requirement to find nth(3rd) lowest salary of an Employee.

Query to get nth(3rd) highest salary of an Employee without using TOP and Sub Query

This question also can twisted to find or fetch nth(3rd) highest salary of an Employee without using TOP and Sub Query so solutions can be using With CTE(Common Table Expressions) as below query.

WITH Salaries AS( SELECT distinct Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) AS 'RowNum'     FROM Employee)SELECT Salary FROM Salaries WHERE RowNum = 3

in above query we find salary in descending order using With CTE(Common Table Expression) than we used RowNum = n(3) which results into nth(3rd) highest salary of an Employee without using TOP and Sub Query.

Query to get nth(3rd) lowest salary of an Employee without using TOP and Sub Query

To find or fetch nth(3rd) lowest salary of an Employee without using TOP and Sub Query so solutions can be using With CTE(Common Table Expressions) as below query.

WITH Salaries AS(SELECT distinct Salary, ROW_NUMBER() OVER(ORDER BY Salary asc) AS 'RowNum'     FROM Employee)SELECT Salary FROM Salaries WHERE RowNum = 3

in above query we find salary in ascending order using With CTE(Common Table Expression) than we used RowNum = n(3) which results into nth(3rd) lowest salary of an Employee without using TOP and Sub Query.