«

»

Dec 14

Find the Nth Highest Salary

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>