Nth Highest Salary - SQL

In SQL, finding the Nth highest salary in a table is a common challenge that tests your understanding of sorting, subqueries, and handling edge cases. The "Nth Highest Salary" problem on LeetCode is a perfect example of this. Let's dive into the problem and explore solutions in different SQL dialects. 

Problem Overview 

We need to write an SQL query to find the Nth highest salary from the Employee table. If there is no Nth highest salary, the query should return null. 

The Employee Table Structure

id	salary
1	100
2	200
3	300

SQL Query Solutions 

The challenge lies in dynamically selecting the Nth salary. This can be achieved using a subquery with LIMIT and OFFSET in MySQL and PostgreSQL, and ROWNUM in Oracle. 

MySQL Solution 

In MySQL, we can create a procedure or use a session variable to dynamically fetch the Nth highest salary:

DELIMITER //

CREATE PROCEDURE getNthHighestSalary(IN N INT)
BEGIN
  SET N = N - 1;
  SELECT
    (SELECT DISTINCT salary
     FROM Employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET N) AS getNthHighestSalary;
END //

DELIMITER ;

Call this procedure with a specific N value to get the Nth highest salary. 

PostgreSQL Solution 

PostgreSQL uses a similar approach to MySQL but does not require changing the delimiter:

CREATE OR REPLACE FUNCTION getNthHighestSalary(N INT) RETURNS INT AS $$
BEGIN
  RETURN (
    SELECT DISTINCT salary
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET N - 1
  );
END;
$$ LANGUAGE plpgsql;

Oracle Solution 

In Oracle, you would use a PL/SQL block with the ROWNUM pseudo-column:

CREATE OR REPLACE FUNCTION getNthHighestSalary (N IN INT) RETURN INT IS
  nth_salary INT;
BEGIN
  SELECT salary INTO nth_salary
  FROM (
    SELECT salary, ROWNUM as rn
    FROM (
      SELECT DISTINCT salary FROM Employee ORDER BY salary DESC
    )
  )
  WHERE rn = N;

  RETURN nth_salary;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

Expected Output 

Example 1: For n = 2 with salaries 100, 200, and 300, the output would be:

getNthHighestSalary(2)
200

Example 2: For n = 2 with a single salary 100, the output would be:

getNthHighestSalary(2)
null

Conclusion 

The "Nth Highest Salary" problem is a great way to understand advanced SQL concepts like subqueries, custom functions, and dynamic offsets. Each SQL dialect has its nuances, but the core logic remains the same. This problem showcases the flexibility and power of SQL in handling complex data retrieval tasks.