Nth Highest Salary

You can write a SQL query to get the nth highest salary from the Employee table using a subquery and the LIMIT and OFFSET clauses. Here’s how you can do it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE nthHighestSalary INT;
  DECLARE offsetValue INT;

  SET offsetValue = N - 1;

  SELECT DISTINCT salary 
  INTO nthHighestSalary
  FROM Employee 
  ORDER BY salary DESC 
  LIMIT 1
  OFFSET offsetValue;

  RETURN nthHighestSalary;
END

This code creates a function that selects the unique salaries from the Employee table, orders them in descending order, and then uses the LIMIT and OFFSET clauses to get the nth highest salary. If there is no nth highest salary, the variable nthHighestSalary will remain NULL, and the function will return NULL.

The reason for using N - 1 in the OFFSET clause is related to how SQL numbering and the requirement for the “nth highest salary” work.

In SQL, OFFSET starts at 0, not 1. So, when you want to retrieve the 1st highest salary, you would use an OFFSET of 0. For the 2nd highest salary, you would use an OFFSET of 1, and so on.

So, the formula N - 1 converts the human-readable “nth” term into the zero-based numbering that SQL expects for the OFFSET.

  • If you want the 1st highest salary, N is 1, so N - 1 gives you an OFFSET of 0.
  • If you want the 2nd highest salary, N is 2, so N - 1 gives you an OFFSET of 1.
  • And so on.

This allows you to skip over the first N - 1 highest salaries and return the Nth one.