function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Vinay JVinay J 

Aggregate child records in lookup

Auction and Bid are objects in lookup where bid is child. For a given auction, I want to find out record id of Bid record with maximum bid amount. Can it be done with aggregate functions? I'm getting correct result when I execute the below mentioned query, but I'm unable to get the record id of bid record.

Select max(Bid_Amount__c) from Bid__c where auction__c='a0617000000MQzm'
Best Answer chosen by Vinay J
RamuRamu (Salesforce Developers) 
The below query will give you the appropriate result

Select id,max(Amount__c) from Bid__c group by id,auction__c having auction__c='a0q90000005JRF9' order by Max(amount__c) desc  limit 1
Please mark this as the best answer if it resolved your issue.

All Answers

SethuSethu
Hi Vinay ,

You haven't query id.

Select id, max(Bid_Amount__c) from Bid__c where auction__c='a0617000000MQzm'
Vinay JVinay J
thias query doesn't works.. Says it has to be grouped by id.. when I group by id, it gives list of all bids..
SethuSethu
Try this,

select id, max(Bid_Amount__c) from Bid__c where auction__c='a0617000000MQzm' groupby auction__c

 
RamuRamu (Salesforce Developers) 
The below query will give you the appropriate result

Select id,max(Amount__c) from Bid__c group by id,auction__c having auction__c='a0q90000005JRF9' order by Max(amount__c) desc  limit 1
Please mark this as the best answer if it resolved your issue.
This was selected as the best answer
Vinay JVinay J
Thanks Sethu, Ramu for your help :)
Vinay JVinay J
@Ramu, your query worked like charm. I need one more help.. If instead of one auction, I have list of auctions, how can I fetch the max(amount) of Bid??
RamuRamu (Salesforce Developers) 
Try using the below query, you may not get the bid id in this query but it gives the max amount value corresponding to each Auction

Select Auction__c,max(Amount__c) from Bid__c group by auction__c order by Max(amount__c) desc
Vinay JVinay J
Yes, you are right.. this query returns auction id with max amount correctly.. but if I add bid id to the query, it returns all the bid records.. is it possible anyhow to get bid id as well?
RamuRamu (Salesforce Developers) 
I tried to no luck :(
Vinay JVinay J
:( .. can you suggest any other way.. basically, what I want to achieve is,

On auction, I'm populating amount and owner's name of highest bid. Right now, I'm doing it through trigger, by fetching all bids associated to auction (SELECT id, Name, Bid_Amount__c, High_Bidder__c, (SELECT ID, Name, Bid_Amount__c, Owner.id FROM Bids__r) FROM Auction__c) and then parsing through them to decide highest bidder. I want to use aggregate finction to achieve the same. Please suggest if you can think on any workaround.