CODEWITHSUNDEEP
hivehiveql
LSG
LSG

Reputation: 127

HiveQL: Select value of column paired with Max(value) of another column

Let´s assume i have this table

Date         Department   Value
2017-01-02    A            30
2017-01-02    B            60
2017-01-02    C            10
2017-01-02    D            40
2017-01-03    C            20
2017-01-03    D            150
2017-01-03    E            100
2017-01-03    F            20
...

And i want to get the department which have a higher 'value' each day

Which would result in

Date          Department   Value
2017-01-02    B            60
2017-01-03    D            150

How could i achieve this?

Upvotes: 0

Views: 1234

Answers (3)

leftjoin
leftjoin

Reputation: 38335

Use rank() analytic function. rank() will assign 1 to rows with higher value per day. 

select Date, Department, Value
from
(
select a.Date, a.Department, a.Value,
       rank() over(partition by a.Date order by a.Value desc) as rnk
  from tableName a
)s
where rnk=1
;

Upvotes: 1

user8406805
user8406805

Reputation:

Your Base data:

hive> create table tx1(date1 date,department string,value int) row format delimited fields terminated by ',';
OK
Time taken: 1.172 seconds
hive> load data local inpath '/home/vivekanand/vivek/hive/test.dat' into table tx1;
Loading data to table default.tx1
OK
Time taken: 0.727 seconds
hive> select * from tx1;
OK
2017-01-02  A   30
2017-01-02  B   60
2017-01-02  C   10
2017-01-02  D   40
2017-01-03  C   20
2017-01-03  D   150
2017-01-03  E   100
2017-01-03  F   20
Time taken: 1.89 seconds, Fetched: 8 row(s)

You can use the analytical function here as below:

select date1,department,value
from(select date1,department,value,rank() over(partition by date1 order by value desc) f from tx1) k
where f=1;

Output:

Total MapReduce CPU Time Spent: 0 msec
OK
2017-01-02  B   60
2017-01-03  D   150
Time taken: 1.569 seconds, Fetched: 2 row(s)

Upvotes: 1

sandeep rawat
sandeep rawat

Reputation: 4957

Break problem in two parts first get max value by date ie CTE.

Join result set with base table and get desired result

ie 

  with  temp as (
          select Date ,max(value) as value
          from tableName group by Date
  )
  selct a.Date  ,  a.Department ,a.Value 
      from tableName a join temp b
      on a.Date=b.Date 
      and a.value=b.value

Upvotes: 1

Related Questions