Dear Deepanjan,

Greetings!

Please try below code.

CREATE TABLE IF NOT EXISTS sampletable (Empl STRING, Depart STRING, salary INT) row format delimited fields terminated by ','

LOAD DATA LOCAL INPATH '/home/edureka/Desktop/file' INTO table sampletable;

select * from
(
SELECT RANK() OVER (PARTITION BY Depart ORDER BY salary DESC) AS Rank, *
FROM sampletable
) AS A WHERE RANK >=4

 

image

Please try and let us know if you face any issue.

We are eagerly waiting for your response.


Regards,
Kamini at Edureka
edureka! Solution Team
On Mon, 17 Jul at 6:24 PM , Teamtech+prod <teamtech+prod@edureka.in> wrote:
Hi Team
There is post ticket which is failed to update in freshdesk.
Please find details below.

User Email:- datta.deepanjan@gmail.com

Post Title:- HIVE query for 3rd height salary of each depertment

Post Content:- I have a file with (Emp,Dept,Salary) fields and the data is like below :

EMP1 IT 10000
EMP2 IT 20000
EMP3 IT 30000
EMP4 IT 50000
EMP5 ADMIN 20000
EMP6 ADMIN 30000
EMP7 ADMIN 44000
EMP8 ADMIN 55000
EMP9 SALES 23000
EMP10 SALES 11000
EMP11 SALES 32000
EMP12 SALES 15000

I want a HIVE query to get the details of the employee who is having the 3rd height salary of each department.

Please help.

403912:41453