# Solution by jcc273

• ### Approach 1: Using row numbering [Accepted]

#### Intuition

Since we will be given a random collection of salaries in any order with any number of duplicates, then we must somehow organize and assign a ranking order to them in order to determine the nth position.

#### Algorithm

The first thing to be done is to obtain a distinct ordered list of salaries so that we can determine what the nth salary is (greatest to least since we are getting nth highest salary):

``````SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC;
``````

Next we need to number this list in its ordered format so that we can indicate a specific record to be selected based on the ordering. This can be done with a user-defined variable. The user variable will be initialized to 0 before running the `SELECT` and incremented inside the `SELECT` to give each row an incrementing id based on this ordering. We can then simply select the nth record by this ranking ID. This gives a final solution of the following:

``````CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE vRank INT;
SET @vRank = 0;
RETURN (
SELECT SALARY
FROM (SELECT @vRank := @vRank+1 as RANK, SALARY
FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC) AS DISTINCT_SALARIES) AS ORDERED_SALARIES
WHERE RANK = N
);
END
``````

### Approach 2: Using `LIMIT` and `OFFSET` [Accepted]

#### Intuition

One of the main features of a database is to provide quick and easy access to certain subsets of records. Being the goal of this problem there might be an easy way to have the database do the work for us. In the case of MySql and other databases such as Sqlite the `LIMIT` and `OFFSET` functions can provide these capabilities.

#### Algorithm

As is the same for approach 1, this table consists of the salaries of all employees, some of which might be equal and some of which might be greater than or less than others in any order. For this reason we must first get a list of distinct salaries in the proper order in the same exact way we do in approach 1:

``````SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC;
``````

Next we can make use of the built-in `LIMIT` and `OFFSET` functions to pull the nth record we desire. However, since an offset will imply a value of 0 for the first record we must deduct 1 from the given input. We cannot do this subtraction in the query so we must declare a variable, perform the subtraction, and then use that variable as the offset. If an offset is out of range we will receive a NULL value in response which is what is desired by the original question. That gives us a final solution of:

``````CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE vTemp INT;
SET vTemp = N-1;
RETURN (
SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC LIMIT 1 OFFSET vTemp
);
END
``````

This can also be written with `LIMIT` and `OFFSET` combined:

``````CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE vTemp INT;
SET vTemp = N-1;
RETURN (
SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC LIMIT vTemp,1
);
END
``````

NOTE: MySQL versions prior to 5.5 do not support using a variable in the `LIMIT` and `OFFSET` function. This means approach 2 will not work in these versions. To use an approach similar to 2 you would need to build the query dynamically substituting in the variable value in the string.

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.