Search code examples
hadoophivehiveqlhive-query

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

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?

Solution

  • 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)