How to get the max values?

[hu]

I have a table like this

Fruits
-------
Apples |Oranges | Bananas
0 | 1 | 0
0 | 1 | 5
0 | 0 | 2
0 | 0 | 1
1 | 2 | 1
1 | 1 | 1
1 | 3 | 6


I need to get the row that points to the max of oranges and Bananas group by Apples.
For eg a correct sql query should give me this result


Apples |Oranges | Bananas
0 | 1 | 5
1 | 3 | 6


I am getting the max(oranges) for each Apples,but not the max(Bananas).The max(bananas) value is not grouping by Apples but the whole table.

Any suggestions how I do this.

Thanks a bunch...in advance

 

[karluk]

You don't give the query you tried, but in this case the obvious approach works:

select apples, max(oranges), max(bananas) from your_table
group by apples;

 

[angiole]

Out on a limb here, but if you are getting the max of bananas for the whole whole table, then you're likely using a third party tool to build your sql.

The max of bananas is not associated (Cognos Impromptu speak) to the group but rather the report.

You may simply have to click something on to retrieve what you need. AA 8~)

 

[carp]

SELECT apples, sum(oranges), sum(bananas)
FROM my_fruity_table
GROUP BY apples
ORDER BY oranges DESC, bananas DESC;