CODEWITHSUNDEEP
hivehiveql
sparker
sparker

Reputation: 1578

HiveQL to find the second largest salary from the employee table?

How to find the second largest salary from the employee table using HiveQl?

Upvotes: 0

Views: 17398

Answers (3)

RushHour
RushHour

Reputation: 613

In case of ties the accepted answer won't work. So below is my code which works in all situations. Just replaced row_number with dense_rank that's it. Want to know more about dense_rank then visit this link

select * from  (SELECT dep_name,salary,DENSE_RANK() over(ORDER BY salary desc) as rank FROM department) as A where rank = 2;

OUTPUT:

+--------+------+----------+
|dep_name|salary|     rank |
+--------+------+----------+
|      CS| 30000|         2|
|   CIVIL| 30000|         2|
+--------+------+----------+

Hope it helps!

Upvotes: 3

Ravindra Phule
Ravindra Phule

Reputation: 51

    SELECT * from(select salary,row_number( ) 
    over (order by salary desc ) 
    as BLAH_no from table where group by salary) T where T.BLAH_no=2;

Upvotes: 2

sparker
sparker

Reputation: 1578

I got my answer on my own. Below is the HQL Query to get the second maximum salary from Employee table.

select firstname,salary from
    (select firstname, salary from employee sort by salary desc limit 2)
     result sort by salary limit 1;

Happy Learning!!

Upvotes: 0

Related Questions