In this article, we will see how to determine the second highest and Nth highest salary from a column in SQL Server 2012. This is a question which is frequently asked in many sites. To get the second highest salary or say nth highest salary there are various ways to write a query in SQL Server. Here, we will see various queries to do that. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns ID, UserName, CompanyName, Salary. Set the identity property=true for ID. The table looks as in the following:

Create-table-in-sqlserver.jpg

Now insert some values in this table. The table looks like this:

Table-in-sqlserver.jpg

To Get Second Highest Salary

Solution1

In this solution first we determine the top 2 salaries from the salary column in descending order. After that we find the min salary from two salaries using the min function. That will be the second highest salary. The following query defines that:

select min(Salary) as SecondHighestSalary from UserDetail where Salary in

(select distinct TOP 2 (Salary) from UserDetail order by Salary desc)

OUTPUT

2nd-highest-salary-solution1-in-sqlserver.jpg

Solution2

In this solution first we determine the max 2 salaries from the salary column. After that we define a condition wherein the max salary is less than from the outer query. That will be the second highest salary. The following query defines that:

select MAX (salary ) from UserDetail where

salary not in(select MAX (salary) from UserDetail )

2nd-highest-salary-solution2-in-sqlserver.jpg

Solution3

This solution is similar to the previous solution. We can also write the previous query as in the following query:

select MAX (salary ) from UserDetail where

salary < (select MAX (salary) from UserDetail );

To Get Nth Highest Salary

To get a specified highest salary we use the following query:

Select min(Salary) salary from UserDetail

where Salary in (

select distinct top(n) salary from UserDetail order by Salary desc

)

Here, n species the count of the highest salaries to retrieve; if we set the value of n to 2 then we get the 2 highest salaries.

To find 2nd highest salary:

Select min(Salary) salary from UserDetail

where Salary in (

select distinct top(2) salary from UserDetail order by Salary desc

)

OUTPUT

2nd-highest-salary-with-nth-higher-salay-in-sqlserver.jpg

To find the 3rd highest salary:

Select min(Salary) salary from UserDetail

where Salary in (

select distinct top(3) salary from UserDetail order by Salary desc

)

OUTPUT

3rd-highest-salary-with-nth-higher-salay-in-sqlserver.jpg

To find the 4th highest salary:

Select min(Salary) salary from UserDetail

where Salary in (

select distinct top(4) salary from UserDetail order by Salary desc

)

OUTPUT

4th-highest-salary-with-nth-higher-salay-in-sqlserver.jpg