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 ;
Apache Lucene, Apache Solr and all other Apache Software Foundation project and their respective logos are trademarks of the Apache Software Foundation.
Elasticsearch, Kibana, Logstash, and Beats are trademarks of Elasticsearch BV, registered in the U.S. and in other countries. This site and Sematext Group is in no way affiliated with Elasticsearch BV.
Service operated by Sematext