# 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.

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.