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