Solution by jcc273


  • 0
    J

    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.


Log in to reply
 

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