Selecting MAX of a Value from multiple categories from a table-CodePudding

I am looking to get the max weight of Apple, Orange, Mango - there could be any number of fruits. Bold items from the table is what i would like to have my query response.

I know this can be done by partitioning the table for example:

SELECT fruits,max(weight) OVER(PARTITION BY fruits) 
FROM fruitstat
GROUP BY fruits;

But this is not getting my expected results. I need the ones which are the max weight fruits in its category.

Fruits	Color	Weight
Apple	red	23
Orange	orange	6
Mango	yellow	13
Apple	red	15
Orange	orange	19
Mango	yellow	16
Apple	red	44
Orange	orange	31
Mango	yellow	12
Apple	red	14
Orange	orange	22
Mango	yellow	11

CodePudding user response：

Just group the MAX(weight) by fruits:

WITH fruit AS
(
  SELECT 'Apple' as fruits,'red' as color ,23 as weight FROM dual UNION ALL
  SELECT 'Orange','orange',6  FROM dual UNION ALL
  SELECT 'Mango','yellow',13  FROM dual UNION ALL
  SELECT 'Apple','red',15     FROM dual UNION ALL
  SELECT 'Orange','orange',19 FROM dual UNION ALL
  SELECT 'Mango','yellow',16  FROM dual UNION ALL
  SELECT 'Apple','red',44     FROM dual UNION ALL
  SELECT 'Orange','orange',31 FROM dual UNION ALL
  SELECT 'Mango','yellow',12  FROM dual UNION ALL
  SELECT 'Apple','red',14     FROM dual UNION ALL
  SELECT 'Orange','orange',22 FROM dual UNION ALL
  SELECT 'Mango','yellow',11  FROM dual
)
SELECT fruits, MAX(weight)
  FROM fruit
 GROUP BY fruits;

P.S. MAX for the apple is 44, not 23, at least in your sample data

CodePudding user response：

You don't need to use group by on window functions. You can do this, instead.

First, sort the fruits weight by using rank.

select rank() over (partition by fruits order by weight) as rank, fruits, weight
from fruitstat

After that, you can use subquery to return the first value only.

select fruits, weight
from (select rank() over (partition by fruits order by weight) as rank, fruits, weight from fruitstat) a
where rank = 1