4

These were the two questions asked to me during an interview but only condition is to use rank/dense_rank.

Find second highest salary in each department using rank/dense_rank in hive.

  1. When there are sufficient records in each department.
  2. When there is only 1 record in few departments.

Guide me if this is even valid or not. If yes then what should be the queries for the same.

Programmeur
  • 190
  • 1
  • 14
  • Possible duplicate of [how to get second highest salary department wise without using analytical functions?](https://stackoverflow.com/questions/31243365/how-to-get-second-highest-salary-department-wise-without-using-analytical-functi) – Skanda Shastry Mar 25 '19 at 10:22
  • The question is by using analytical functions (rank/dense_rank) so it's a bit different from without using analytical functions (how to get second highest salary department wise without using analytical functions?) – Programmeur Mar 26 '19 at 12:27

2 Answers2

4

If you need to select all second salary employees then use

dense_rank() over(partition by department order by salary desc) = 2 filter. It will return all employees with second salary.

When there is only 1 record in the department (no second salary exists, one employee in the department), it will be ranked 1 and you will get no records for that department filtering by dense_rank = 2.

If you need to select only one record (not all employees) with second salary then row_number() over(partition by department order by salary desc) = 2 will work, but it will pick one random employee with second salary if there are many employees with second salary. Only one record will be marked with row_number = 2.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
0

Try this:

WITH RESULT AS
(
SELECT salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS SecondHighest FROM Employee
)
SELECT IFNULL(( SELECT salary FROM RESULT WHERE SecondHighest = 2 ORDER BY salary) , NULL) as SecondHighestSalary
Deepak Ananth
  • 15
  • 7