CODEWITHSUNDEEP
hadoophiveql
Mahesh Kumar
Mahesh Kumar

Reputation: 3

How to find top 3 employee in every department having higest salary in hive?

name, salary, department
joe, 95000, Manager
walter, 26500, HR
Adam, 36520,Developer
Sam, 38910, Developer
Romie, 68457,Manager
oliver, 69545, Developer
Mahesh, 89542, Developer
Ricky, 96845,HR
Sara, 84756,Manager
Anand, 36597, HR
Kevin, 98457, HR
Ram, 63547, Manager

output should be `

Developer, Mahesh, 89542
Developer, oliver, 69545
Developer, Sam, 38910
HR, Kevin, 98457
HR, Ricky, 96845
HR, Anand, 36597
Manager, joe, 95000
Manager, Sara, 84756
Manager, Romie, 68457

`

I want all the record should be printed like the given output, and this should run in hive.

Upvotes: 0

Views: 191

Answers (1)

Akash Jain
Akash Jain

Reputation: 55

select dept, name, salary from (
    select name, salary, dept, row_number() over(partition by dept order by salary desc) as rn 
    from employee_sal
    ) emp 
where emp.rn<4;

Result will be look like below:

name salary dept Mahesh 89542 Developer oliver 69545 Developer Sam 38910 Developer Kevin 98457 HR Ricky 96845 HR Anand 36597 HR joe 95000 Manager Sara 84756 Manager Romie 68457 Manager

Upvotes: 1

Related Questions