CODEWITHSUNDEEP
sqlhivehiveqlgreatest-n-per-group
VENKY
VENKY

Reputation: 1

Write SQL & Hive query to print the year in which the 2nd highest salary was paid for each country?

Write SQL and HIVE query to print the year in which the 2nd highest salary was paid for each country?.

Please provide query for the below table

country,salary,year
india,1000,2017
japan,2000,2017
germany,1500,2017
india,1250,2018
japan,500,2018
china,955,2017
japan,850,2019
china,1150,2018
india,1250,2019

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

The big question is how you handle ties. Presumably, you mean the second highest distinct salary. In that case, you are specifically looking for the dense_rank() window function:

select t.*
from (select t.*,
             dense_rank() over (partition by country order by salary desc) as seqnum
      from t
     ) t
where t.seqnum = 2;

Now, the challenge with this is that it could return more than one row in the event of ties. If you specifically want one row, then:

select t.*
from (select t.*,
             dense_rank() over (partition by country order by salary desc) as ranking,
             row_number() over (partition by country, salary order by country) as seqnum
      from t
     ) t
where t.ranking = 2 and seqnum = 1;

Upvotes: 0

User9123
User9123

Reputation: 1733

something like:

select 
    t.*
from (
    select
        tbl.*,
        row_number() over(partition by country order by salary desc) rn
    from 
        tbl
) t
where 
    t.rn = 2

Upvotes: 1

Related Questions