hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Furcy Pin <furcy....@flaminem.com>
Subject Re: Finding Max of a column without using any Aggregation functions
Date Wed, 23 Apr 2014 09:51:33 GMT
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