Click here to Skip to main content
15,072,735 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
0
down vote
favorite
my input

Department     Jan_sal      Feb_sal         Mar_sal

civil            1            5               5
mech             2            7               2
civil            3            8               9
mech             6            4               4
mech             5            6               6
Elec             4            5               3
mech             8            5               5
Elec             8            5               4
Expected output

Civil       Mar
Mech      Feb
Elec       Jan


What I have tried:

Im new in hive.

But im trying to write subquery and i tried below

select    department
         ,sort_array
          (
              array
              (
                  struct(-sum(Jan_sal),'Jan')
                 ,struct(-sum(Feb_sal),'Feb')
                 ,struct(-sum(Mar_sal),'Mar')
              )
          )[0].col2

from      mytable

group by  department


but im getting error like --->
Argument type mismatch ''mar_sal'': Argument 1 of function SORT_ARRAY must be array<PRIMITIVE>, but array<struct<col1:bigint,col2:string>> was found.
Posted
Comments
Richard MacCutchan 12-Apr-17 7:21am
   
The error message is telling you what the problem is, you need to change your array statement to match the rules.

1 solution

here is the solution for the above problem


select d.department,
       case
       when (d.maxJan>=d.maxFeb)
       and (d.maxJan>=d.maxMarch)
       then 'Jan'
       when (d.maxFeb>=d.maxJan)
       and (d.maxFeb>=d.maxMarch)
       then 'Feb'       
       when (d.maxMarch>=d.maxJan)
       and (d.maxMarch>=d.maxFeb)
       then 'March'
       else 'null'
       end as month 
       from (select department,max(jan_sal) maxJan,max(feb_sal) maxFeb,max(march_sal) maxMarch from mytable group by department)d;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900