CODEWITHSUNDEEP
sqlhive
jumpman8947
jumpman8947

Reputation: 581

hive sql get min and max values of multiple records

I have a query which results are 

fruit           street             inventory            need      to_buy
banana          123                15                   99        22
apple           4                  32                   68        44
banana          789                01                   32        11
apple           9832               0                    99        94
apple           85                 839                  12        48
banana          832                77                   05        55

I want to get the minimum values for inventory, and need, and get the max to_buy value. but only have one record of each 'fruit'. the 'street' column is irrelevant and is not needed in the final result. The final result should look like

fruit            inventory(min)            need(min)      to_buy(max)
banana           01                        05             55
apple            0                         12             94

Also the initial records may not be ordered at first so there are more 'fruits' inserted at random How can i achieve the desired result above?

Upvotes: 0

Views: 5554

Answers (2)

Tanvir
Tanvir

Reputation: 174

This one should work:

SELECT fruits, MIN(inventory), MIN(need), MAX(to_buy)
FROM <table_name>
GROUP BY fruits

Upvotes: 0

Rahul Jain
Rahul Jain

Reputation: 1399

Try this:

SELECT MIN(inventory), MIN(need), MAX(to_buy)
FROM tableName
GROUP BY fruits

Upvotes: 1

Related Questions