SQL- nth highest salary

Write a SQL query to get the nth highest salary from the Employee table.

 

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

 

ANALYSIS:

The syntax is a bit different from other languages such as Python and R. However, SQL is very direct. Hence, I am going to skip the explanation part for the syntax.

 

There are many ways to get the Nth number of something.

For instance, to get 2nd highest Salary I can use the following methods:

SELECT 
	MAX(Salary) as SecondHighestSalary -- for next case i just use SHS
FROM 
	Employee
WHERE 
	Salary < (SELECT MAX(Salary) FROM Employee) 
    		 -- OR 
             -- (SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1)
/*             
OR 

SELECT 
	Salary as SHS
FROM 
  	Employee
ORDER BY 
	Salary DESC 
LIMIT 
	1,1
*/

Or even use either rank or dense rank, which I covered previously. (So I am going to skip)

Yet, those methods are a bit dull and inefficient compared to using a function.

 

Anyway, let's get to start!

Here, I created a new variable M, which is N -1. It is necessary because when N = 1 that means to get the 1st salary instead of 2nd. See the LIMIT 1 OFFSET M

 

There is a chance that given N is greater than the number of rows. That is, N is asking beyond the data. In those cases, I need to print null.

 

Now, all I need to do is return the 1 item, which is set by LIMIT, after skipping M items, which is set by OFFSET.

 

NOTE: I am still trying to understand why I cannot simply use LIMIT 1,M, instead of spelling out OFFSET. In normal cases, I can just use 1,1 to represent limit 1 and offset 1.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE M INT;
    -- setting M for later use LIMIT and OFFSET
    SET M = N - 1;
  RETURN (

      SELECT 
        Salary
      FROM
         (SELECT DISTINCT
         -- return null if N is outside of scope, otherwise return Salary
            IF(N > (SELECT COUNT(DISTINCT Salary) FROM Employee), null, Salary) as Salary
          FROM
            Employee
         )temp
      ORDER BY 
        Salary DESC
      LIMIT 
        1 OFFSET M -- here, I must type out OFFSET, instead of using 1,M
  );
END