## See on following table records

Emp1 Table

Emp ID Salary

1 10000

2 25000

3 5000

4 60000

5 45000

find the 2nd highest salary in SQL

Now, write query for finding second highest number:

SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

Result

450

## An alternative solution

We can actually use the not equals operator “<>” – instead of the NOT IN.

SQL Query :

select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )

## Nth highest salary

The SQL below will give you the correct answer – but you will have to plug in an actual value for N of course. This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS:

FROM Employee Emp1 WHERE (N-1) = (SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

## Finding nth highest salary example

we are first see the 2nd highest Salary value in our table above, so our N is 2. This means that the query will look like this:

SELECT * FROM Employee Emp1 WHERE (1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

## Find the nth highest salary using the {TOP} in SQL Server

We can also use the TOP keyword (for databases that support the TOP keyword, like SQL Server) to find the nth highest salary. Here is some fairly simply SQL that would help us do that:

SELECT TOP 1 Salary FROM ( SELECT DISTINCT TOP N Salary FROM Employee ORDER BY Salary DESC ) AS Emp ORDER BY Salary

## Find the nth highest salary in MySQL

In MySQL, we can just use the LIMIT clause along with an offset to find the nth highest salary. If that doesn’t make sense take a look at the MySQL-specific SQL to see how we can do this:

SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1

## Find the nth highest salary in SQL Server

In SQL Server, there is no such thing as a LIMIT clause. But, we can still use the offset to find the nth highest salary without using a subquery – just like the solution we gave above in MySQL syntax. But, the SQL Server syntax will be a bit different. Here is what it would look like:

SELECT Salary FROM Employee ORDER BY Salary DESC OFFSET N-1 ROW(S) FETCH FIRST ROW ONLY