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.subraman...@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 > > >
