CODEWITHSUNDEEP
hadoophivehiveqlhive-query
sheharbano
sheharbano

Reputation: 219

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?

Upvotes: 0

Views: 107

Answers (1)

Ben Watson
Ben Watson

Reputation: 5531

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)

Upvotes: 1

Related Questions