❤ ️ the original ❤ ️

Write an SQL query to get the NTH highest Salary in the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Copy the code

For example, if n = 2 in the Employee table above, the second highest salary, 200, should be returned. If no NTH salary exists, the query should return NULL.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+
Copy the code

⭐️ ⭐️

Dense_rank ⭐️ LeetCode solution series ⭐️ 178. Score ranking (Oracle dense_rank function), the problem is still the ranking function solution.

Dense_rank = dense_rank = dense_rank

select distinct salary,dense_rank(a)over (order by salary desc) rank 
from employee;
Copy the code

② Take the query result as a table, input N value to query again, and usenvlThe function returnsnull, returns the result:

select nvl(salary,null) 
    from (
        select distinct salary,dense_rank(a)over (order by salary desc) rank 
        from employee
    )
    where rank = 2;
Copy the code

③ The complete code is as follows:

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
    /* Write your PL/SQL query statement below */
    select nvl(salary,null) 
    into result 
    from (
        select distinct salary,dense_rank(a)over (order by salary desc) rank 
        from employee
    )
    where rank = N;
    
    RETURN result;
END;
Copy the code

Go to LeetCode and see the results:

❄️ comes at the end ❄️

This is a very difficult problem to solve, and once you know the function, you can see the solution immediately.

---

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.