CODEWITHSUNDEEP
hadoophivehiveqlrankdense-rank
Programmeur
Programmeur

Reputation: 190

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

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.

Upvotes: 4

Views: 2032

Answers (2)

Deepak Ananth
Deepak Ananth

Reputation: 15

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

Upvotes: 0

leftjoin
leftjoin

Reputation: 38335

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.

Upvotes: 4

Related Questions