Select max query returning all the rows in a table in Apache Hive

Question

I am querying my data using this query

SELECT date_col,max(rate) FROM crypto group by date_col ;

I am expecting a single row but it is returning all the rows in the table. What is the mistake in this query?

Answer 1

You'll get one row per date_col because you're grouping by it. If you just want the maximum rate then just do SELECT max(rate) FROM crypto;.

If you want to get the date_col for that record too then:

SELECT 
  date_col,
  rate
FROM crypto
WHERE rate = (SELECT MAX(rate) FROM crypto)