Nth Highest Salary - PostgreSQL Solution

1. Introduction

This blog post addresses a common SQL query challenge: finding the nth highest salary in a PostgreSQL database using the Employee table. This kind of query is often encountered in data analysis and database management.

Problem

We need to create a PostgreSQL solution to find the nth highest salary from the Employee table. If the nth highest salary does not exist, the query should return null.

Database Table:

Employee Table:

id	salary
1	100
2	200
3	300

3. PostgreSQL Solution

CREATE OR REPLACE FUNCTION getNthHighestSalary(N INT) RETURNS INT AS $$
DECLARE
    nthSalary INT;
BEGIN
    SELECT salary INTO nthSalary FROM (
        SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
        FROM Employee
    ) as temp
    WHERE rn = N;

    RETURN nthSalary;
END;
$$ LANGUAGE plpgsql;

Output:

Output for n = 2:

getNthHighestSalary(2)
200

Output for n = 2 with only one employee:

getNthHighestSalary(2)
null

Explanation:

1. The getNthHighestSalary function is created to find the nth highest salary in PostgreSQL.

2. It uses the ROW_NUMBER() window function to assign a unique row number to each salary in descending order.

3. The function then selects the salary where the row number equals N.

4. If the nth highest salary does not exist, the function returns null.