I did a Leetcode challenge today instead of a HackerRank challenge cause I find that the challenges in HackerRank are not easily understandable whereas description of challenges in Leetcode are short and concise.

The challenge: Given the ID no. and Salary, I had to find out the nth highest salary.

ID	Salary
589	100
590	200
591	300

Expected Output:

getNthHighestSalary(2)

200

My initial code which did not give the expected output is as follows:

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) 
 RETURN NUMBER 
 IS result NUMBER;
 BEGIN
     SELECT salary
     INTO result
     FROM (SELECT DISTINCT salary
           FROM Employee order by salary desc)
     WHERE ROWNUM = N and ROWNUM <=1;
     RETURN result;
 END getNthHighestSalary;

When tested against a query to fetch the second highest salary, it returns a null. I can’t figure out why it is failing. I will have to approach my colleague for guidance. The other solution that did work out was from the discussion forums and below are the solutions.

Solution 1:

 CREATE FUNCTION getNthHighestSalary(N IN NUMBER) 
 RETURN NUMBER 
 IS result NUMBER;
 BEGIN
     /* Write your PL/SQL query statement below */
     SELECT salary
     INTO result
     FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY Salary Desc) 
           as r
           FROM Employee)
     WHERE r = N and ROWNUM <=1;
     RETURN result;
 END getNthHighestSalary; 

Breaking down the code:

Query 1:

This was my first time encountering a function so I had to do a little study. Besides, readily available functions like LEN(), MAX() and so on, users are also able to create user defined functions. The skeleton to create a user defined functions looks like below as taken from a source.

 CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
 RETURN return_datatype
 IS | AS
 [declaration_section]
 BEGIN
    executable_section
 [EXCEPTION
    exception_section]
 END [function_name]; 

Example of creating a function. Source

The following statement creates the function get_bal on the sample table oe.orders (the PL/SQL is in italics)

 CREATE FUNCTION get_bal(acc_no IN NUMBER) 
    RETURN NUMBER 
    IS acc_bal NUMBER(11,2);
    BEGIN 
       SELECT order_total 
       INTO acc_bal 
       FROM orders 
       WHERE customer_id = acc_no; 
       RETURN(acc_bal); 
     END; 

Hence in this solution, I have created a function called ‘getNthHighestSalary” which is of the datatype Number and returns the variable ‘result’.

 CREATE FUNCTION getNthHighestSalary(N IN NUMBER) 
 RETURN NUMBER 
 IS result NUMBER;

Query 2:

Another aspect of the solution is the understanding of the ‘ROWNUM’ and ‘DENSE_RANK()’.

The function ‘ROWNUM’ returns a number of a selected row from a table or joined table. Source

SELECT salary, ROWNUM 
FROM Employee
ORDER BY salary DESC;

ID	Salary	ROWNUM
591	300	1
590	200	2
589	100	3

The ‘DENSE_RANK()’ function on the other hand, assigns rank consecutively to each of the records without skipping any numbers. There is another function called ‘RANK’ which behavies similar to a ‘DENSE_RANK()’ function however, if there are two records with the same ranking, the rank number is skipped. The following point taken from a stackoverflow perfectly illustrates the point.

with q as (
 select 10 deptno, 'rrr' empname, 10000.00 sal from dual union all
 select 11, 'nnn', 20000.00 from dual union all
 select 11, 'mmm', 5000.00 from dual union all
 select 12, 'kkk', 30000 from dual union all
 select 10, 'fff', 40000 from dual union all
 select 10, 'ddd', 40000 from dual union all
 select 10, 'bbb', 50000 from dual union all
 select 10, 'xxx', null from dual union all
 select 10, 'ccc', 50000 from dual)
 select empname, deptno, sal
      , rank() over (partition by deptno order by sal nulls first) r
      , dense_rank() over (partition by deptno order by sal nulls first) dr1
      , dense_rank() over (partition by deptno order by sal nulls last) dr2
  from q;

EMP	DEPTNO	SAL	R	DR1	DR2
Lalice	10		1	1	4
Jenny	10	10000	2	2	1
Minzy	10	40000	3	3	2
Chuck	10	40000	3	3	2
Tom	10	50000	5	4	3
Janice	10	50000	5	4	3
Lulu	11	500	1	1	1
Mike	11	20000	2	2	2
Matt	12	30000	1	1	1

With the partiton by arugument missing, the below query ranks the salary by descending order.

SELECT salary, DENSE_RANK() OVER (ORDER BY Salary Desc) 
     as r
     FROM Employee;

ID	Salary	r
591	300	1
590	200	2
589	100	3

Query 3: The query returns the nth highest salary and passes it to the variable ‘result’.

SELECT salary
     INTO result
     FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY Salary Desc) 
           as r
           FROM Employee)
     WHERE r = N AND ROWNUM <=1;

Output:

ID	Salary	r
591	300	1

It may seem unnecessary to limit selection to only one row, ‘ROWNUM <=1’. In real world, there could be multiple records with the same salary and hence, same rank, hence, we only want to display the first nth salary.

Solution 2: This was another solution in the forum but was originally submitted for MSSQL so I translated it into Oracle and this solution has a much faster runtime than the first solution.

 CREATE FUNCTION getNthHighestSalary(N INT) 
 RETURN INT
 IS result INT;
 BEGIN
       WITH ranks AS
       (SELECT
         salary,
         row_number() OVER (ORDER BY salary DESC) AS salaryRank
       FROM
         (SELECT DISTINCT
             salary
          FROM employee) )
     SELECT salary
     INTO result
     FROM (
       SELECT
         salary
       FROM
         ranks
       WHERE
         ranks.salaryRank = N);
   RETURN result;
 END; 

The above solution is pretty intuitive hence, I will not be breaking down the queries.

I have been wanting to take the Oracle SQL certification for sometime and have been doing research on it. I will create a post to share on it.

It’s a debate as to whether certification are good investments. In my perspective, these certification are motivation to strive for excellence at the same time a proof of your knowledge. That ofcourse may not necessarily mean that you are good which I believe can only be acquired by experience and trial and error experiments.

• Facebook
• Instagram
• LinkedIn
• GitHub