hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sanjay Subramanian <sanjaysubraman...@yahoo.com>
Subject Re: Finding Max of a column without using any Aggregation functions
Date Wed, 23 Apr 2014 14:49:00 GMT
Thanks 
For the sake of this question I wanted to avoid all order by and limit syntax 😄. It's more
of a challenge question 
Regards
Sanjay 

Sent from my iPhone

> On Apr 23, 2014, at 2:51 AM, Furcy Pin <furcy.pin@flaminem.com> wrote:
> 
> Hi,
> 
> note that if your table contains the max value several time, all the occurences will
be returned. Note also that if it contains a null it will be returned too.
> 
> How about this? 
> 
>  SELECT score FROM student ORDER BY score DESC LIMIT 1 ;
> 
> 
> Note that on this query Impala is incoherent with Hive or mySQL, since Impala considers

> that NULL is greater than everything while Hive and mySQL consider NULL to be smaller
than everything
> 
> And just for fun, you can also try in Hive:
> 
> FROM 
> (
>   SELECT score FROM student
>   DISTRIBUTE BY '1'
>   SORT BY score DESC
> ) M
> SELECT score
> LIMIT 1
> ;
> 
> FROM 
> (
>   SELECT score FROM student
>   DISTRIBUTE BY '1'
> ) M
> REDUCE score USING 'sort -rn | head -1' AS score 
> ;
> 
> The second is just to demonstrate the possibilities of custom reduce, but is greatly
inefficient (in speed and memory).
> And be sure to use SET mapred.reduce.tasks=1 ; before if you don't want idle reduce tasks...
> 
> (I guess I have some spare time too!)
> 
> 
> 
> 2014-04-23 3:27 GMT+02:00 Subramanian, Sanjay (HQP) <sanjay.subramanian@roberthalf.com>:
>> Hey guys
>> 
>> TABLE=STUDENT
>> COLUMN=SCORE
>> 
>> U want to find the max value in the column without using any aggregation functions.
>> 
>> Its easy in a RDB context but I was trying to get a solution in Hive (clearly I have
some spare time on my hands - LOL) 
>> 
>> select 
>>      nfr.score
>> from
>>      student nfr
>> left outer join
>>      (select 
>>           a.score as fra,
>>           b.score as frb
>>      from 
>>           (select
>>                '1' as dummy,
>>                score
>>           from
>>                student
>>           ) a
>>                
>>      join
>>           (select
>>                '1' as dummy,
>>                score
>>           from
>>                student
>>           ) b
>>      ON
>>           a.dummy = b.dummy
>>      where   
>>           a.score < b.score
>>      ) frab
>> on
>>      frab.fra=nfr.score
>> where 
>>      frab.fra is null
>> 
>> Thanks
>> Warm Regards
>> 
>> Sanjay
> 

Mime
View raw message