question Re: How to find nth highest salary in hive. in Archives of Support Questions (Read Only)

How to find nth highest salary in hive.

bsuresh — Tue, 22 Dec 2015 19:45:32 GMT

Hi guys,

I know hive is Data Warehousing tool which is not suited as OLTP. In regular RDBMS Structure i have way like "Correlated queries" helps to find nth highest salary from a table as following.

Is there any way in hive also.The query is related to true/false conditions.

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

Re: How to find nth highest salary in hive.

jstraub — Tue, 22 Dec 2015 20:55:17 GMT

. @Suresh Bonam yes you can calculate the nth element, see the following example:

Create sample table (emp=employees)

create table emp(id Int, salary Double)
row format delimited 
  fields terminated by ',' 
  stored as textfile

I have added the following data to that table:

Understanding the data

Lets look at the sorted salaries first:

select * from emp order by salary;

Result:

emp.id	emp.salary
6	1
2	23
4	84
11	84
1	423
7	578
10	756
9	4445
8	8877
3	67878
5	9999999

Note: We have two identical salaries in the table (=84)

Create query to select unique salaries

In order to select the nth-element we need to remove the duplicate values (e.g. distinct) and create some kind of row counter or index. I decided to use Group-By to remove the duplicate salaries and row_number-UDF to generate the index.

select salary, ROW_NUMBER() over (ORDER BY salary) as row_no from emp group by salary

Result:

salary	row_no
1	1
23	2
84	3
423	4
578	5
756	6
4445	7
8877	8
67878	9
9999999	10

Putting everything together

select * from (
  select salary, ROW_NUMBER() over (ORDER BY salary) as row_no from emp group by salary) res 
where res.row_no = 4

Result:

res.salary    res.row_no
423	      4

Note: We received the correct result and not 84 since we've removed the duplicate salaries in the subquery 🙂

Re: How to find nth highest salary in hive.

gbraccialli3 — Tue, 22 Dec 2015 22:35:45 GMT

Awesome! Marked as favorite post now. 🙂

Re: How to find nth highest salary in hive.

jstraub — Wed, 23 Dec 2015 01:15:18 GMT

Thanks 🙂

Re: How to find nth highest salary in hive.

ssatya618 — Tue, 29 Mar 2016 19:52:32 GMT

hi i tried this but got the below error. pls do help me out. I am quite new to hive.

hive> select salary, ROW_NUMBER() over (ORDER BY salary) as row_no from emp group by salary; FAILED: ParseException line 1:33 mismatched input '(' expecting FROM near 'over' in from clause

Re: How to find nth highest salary in hive.

nikhilaggarwal — Wed, 15 May 2019 02:57:57 GMT

Use DENSE_RANK instead of ROW_NUMBER() with GROUP BY , as it will take only 1 map-reduce job to achieve it as compared to 2 map-reduce jobs by latter one.

select * from (

select salary, DENSE_RANK() over (ORDER BY salary DESC) as row_no from emp) res

where res.row_no = 2;

Re: How to find nth highest salary in hive.

LetsHadoop — Thu, 13 Feb 2020 19:33:01 GMT

Ho to find 2nd(nth) highest salary in each department? Please find below sample dataset.

1,423,depA
2,23,depA
3,67878,depB
11,84,depB
5,9999999,depA
9,4445,depA
8,8877,depB
10,756,depC
4,84,depC
7,578,depA
6,1,depC

output-

10,756,depC
4,84,depC
6,1,depC

Re: How to find nth highest salary in hive.

ask_bill_brooks — Fri, 14 Feb 2020 00:35:20 GMT

Hi @LetsHadoop,

As this thread is older and was marked 'Solved' in Dec 2015 you would have a better chance of receiving a helpful answer by starting a new thread. This will also provide you with an opportunity to provide details about your table design that could aid others in providing an answer more directly tailored to your question.

Re: How to find nth highest salary in Mysql.

loekshagrawal — Tue, 23 Jun 2020 13:24:43 GMT

Please watch the below video:-

https://www.youtube.com/watch?v=fBeN8VQm0_Q

Please like comment share and subscribe my channel, If you loved it.