title: Get row with max value in Hive/SQL?
body: <p>I'm new to Hive/SQL, and I'm stuck on a fairly simple problem. My data looks like:</p>
  
  <pre><code>+------------+--------------------+-----------------------+
  | carrier_iD |     meandelay      |     meancanceled      |
  +------------+--------------------+-----------------------+
  | EV         | 13.795802119653473 | 0.028584251044292006  |
  | VX         | 0.450591016548463  | 2.364066193853424E-4  |
  | F9         | 10.898001378359766 | 0.00206753962784287   |
  | AS         | 0.5071547420965062 | 0.0057404326123128135 |
  | HA         | 1.2031093279839498 | 5.015045135406214E-4  |
  | 9E         | 8.147899230704216  | 0.03876067292247866   |
  | B6         | 9.45383857757506   | 0.003162096314343487  |
  | UA         | 8.101511665305816  | 0.005467725574605967  |
  | FL         | 0.7265068895709532 | 0.0041141513746490044 |
  | WN         | 7.156119279121648  | 0.0057419058192869415 |
  | DL         | 4.206288692245839  | 0.005123990066804269  |
  | YV         | 6.316802855264404  | 0.029304029304029346  |
  | US         | 3.2221527095063736 | 0.007984031936127766  |
  | OO         | 6.954715814690328  | 0.02596499362466706   |
  | MQ         | 9.74568222216328   | 0.025628100708354324  |
  | AA         | 8.720522654298968  | 0.019242775597574157  |
  +------------+--------------------+-----------------------+
  </code></pre>
  
  <p>I want Hive to return the row with the meanDelay max value. I have:</p>
  
  <pre><code>SELECT CAST(MAX(meandelay) as FLOAT) FROM flightinfo;
  </code></pre>
  
  <p>which indeed returns the max (I use cast because my values are saved as STRING). So then:</p>
  
  <pre><code>SELECT * FROM flightinfo WHERE meandelay = (SELECT CAST(MAX(meandelay) AS FLOAT) FROM flightinfo);
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>FAILED: ParseException line 1:44 cannot recognize input near 'select' 'cast' '(' in expression specification
  </code></pre>