CODEWITHSUNDEEP
hadoophivehiveql
Pragadeesh
Pragadeesh

Reputation: 113

Hive : group column based on max value

I have a table with fields as 

date       value
10-02-1900 23
09-05-1901 22
10-03-1900 10
10-02-1901 24

....

I have to return maximum value for each year i.e.,

1900 23
1901 24

I tried the below query but getting wrong ans.

SELECT YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(date,'dd-mm-yyyy'))) as date,MAX(value) FROM teb GROUP BY date;

Can anyone suggest me a query to do this?

Upvotes: 1

Views: 550

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Option 1

select      year(from_unixtime(unix_timestamp(date,'dd-MM-yyyy'))) as year
           ,max(value)                                             as max_value
from        t
group by    year(from_unixtime(unix_timestamp(date,'dd-MM-yyyy')))
;

Option 2

pre Hive 2.2.0

set hive.groupby.orderby.position.alias=true;

as of Hive 2.2.0

set hive.groupby.position.alias=true;

select      year(from_unixtime(unix_timestamp(date,'dd-MM-yyyy'))) as date
           ,max(value)
from        t
group by    1
;

+------+-----------+
| year | max_value |
+------+-----------+
| 1900 |        23 |
| 1901 |        24 |
+------+-----------+

P.s.

Another way to extract the year:

from_unixtime(unix_timestamp(date,'dd-MM-yyyy'),'yyyy')

Upvotes: 1

Related Questions