SQL Leet Code Nth Highest Salary Problem

Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

The query result format is in the following example.

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

Also note that, we have given a function body that we need to use to complete our solution.

The function code is given below.

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (

)

END;

Basically, we need to complete the scalar function code, which will give us Nth highest salary for provided input value.

Solution:

Lets create a table named Employee first then insert sample records into this table.

create table employee
(id int, salary int)

insert into employee (id, salary) values
(1, 100),
(2, 200),
(3, 300)

Now we have a table named employee as shown below.

select * from employee

Now we will user given function and add SQL query inside function body as shown below.

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
SELECT DISTINCT salary FROM (
SELECT DENSE_RANK() OVER(ORDER BY salary DESC) AS getNthHighestSalary, salary
FROM employee )a WHERE getNthHighestSalary = @N
)

END;

Here we have completed a scalar function, now return a nth highest salary for provided input value.

Lets see the 2nd highest salary.

SELECT [dbo].[getNthHighestSalary](2) AS 'getNthHighestSalary 2'

You can see, it returns the second highest salary.

Lets check the case, when there is no nth highest salary available in table.

As we have only three records in table, lets see the 6th highest salary.

SELECT [dbo].[getNthHighestSalary](6) AS 'getNthHighestSalary 6'

 

You can see, it return null as there is no record exist in table for 6th highest salary.

Also Read..

SQL Server Interview Questions and Answers

USER DEFINED Function

 

 623 total views,  1 views today