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...
At any rate, you could replace your dummy join by a cross join:
SELECT nfr.score FROM student nfr LEFT OUTER JOIN ( SELECT a.score as fra FROM student a CROSS JOIN student b WHERE a.score < b.score ) frab ON frab.fra = nfr.score WHERE frab.fra is null and nfr.score is not null ;