function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Gnaneswar TGnaneswar T 

How to get the last modified opportunities from a set of accounts?

I have a set of accounts. I want to get last modified opportunity from each account. Is it possible to get in single query?
SravsSravs
Opportunity lastmodifiedopp = [select id, name, lastmodifiedDate from opportunity where AccountID in: <set of AccountIDs> Order By lastmodifiedDate DESC limit 1 ]
modify the above query @ accountID set 
Grazitti TeamGrazitti Team
Hi Gnaneswar,

Yes it is poosible to get  last modified opportunity from each account.

Loop through your accounts and use following query
      list<opportunity> getlastmodifiedopp = [select id from opportunity where account.id=acc.id order by lastmodifieddate desc limit 1];


Please let us know if you have any question .
please don't Forget to Mark this as your best answer if it works fine for you

Regards,
Grazitti Team
Web: http://www.grazitti.com
Gnaneswar TGnaneswar T
Thanks for quick reply. But that is not what I want. I want to get one opportunity from every account. If I have 10 accounts and each account is associated with 2 opportunities, then I need to get 10 opportunities, one from each account which should be the last modified opportunity
Grazitti TeamGrazitti Team
Hi Gnaneswar T,

so if you want latest modified opportunity then you need to order it by DESC , and if you want last modified opportunity then you will order it by ASC

Regards,
Grazitti Team
Web: http://www.grazitti.com
SravsSravs
I hope this helps  
list<Account> accountsList = [Select id, Name, (Select Id, name From Opportunities order By lastmodifiedDate desc limit 1) From Account ];

//toAccess the opportunitinity related to individual account, 
for(Account acc: accountsList){
	if(acc.Opportunities.size()>0){
		Opportunity relatedOpp = acc.Opportunities[0];
		system.debug('relatedOpp'+relatedOpp);
	}
}
Grazitti TeamGrazitti Team
Hi Gnaneswar,

Here is the example code, to fetch each last modified opportunity  associated with each account

set<id> getAccounts = new set<id>();
map<id,list<opportunity>> accwithLastmodifiedopp = new map<id,list<opportunity>>();
for(account a : [select id from account])
{
    getAccounts.add(a.id);
    accwithLastmodifiedopp.put(a.id,new list<opportunity>());
}
for(opportunity opp: [select id,accountid,lastmodifieddate from opportunity where accountid in : getAccounts order by lastmodifieddate desc limit 1])
{  
    accwithLastmodifiedopp.get(opp.accountid).add(opp);
}
system.debug(accwithLastmodifiedopp);

(you can order it by ASC or DESC, modify it as per your requirement)
please don't Forget to Mark this as your best answer if it works fine for you
Regards,
Grazitti Team
Web: http://www.grazitti.com
Gnaneswar TGnaneswar T
Hi Grazitti Team,
Your logic would give only one opportunity finally but I want one opportunity for each account.

Hi Sravan,

Your logic is considerable. Let's consider the below scenario also. If I have an object AccOppty__c with 2 lookup relationships to Account and Opportunity. I want to get all unique records grouped by account and opportunity, i.e., if I have 4 records on AccOppty__c, 2 of those records have Account Acc1 and Opportunity Opp1 associated with them and 2 other records have Account acc2 and Opportunity opp2 associated with them, I want to get a record associated with Acc1 and Opp1 having latest lastmodifieddate and a record associated with Acc2 and Opp2 having latest lastmodifieddate using soql. Do you have any possible way of doing this in easy way with minimum number of queries?

Thanks in advance!!
Grazitti TeamGrazitti Team
Hi Gnaneswar T

use the following code:-

map<id,opportunity> accwithLastmodifiedopp = new map<id,opportunity>();
for(Account ac: [select id ,(select id from opportunities order by Lastmodifieddate limit 1) from account where id in :<set of accountIds>])
{
if(!ac.opportunities.isEmpty())
accwithLastmodifiedopp.put(ac.id, ac.opportunities[0]);
}
system.debug(accwithLastmodifiedopp);


Regards,
Grazitti Team
Web: http://www.grazitti.com
SravsSravs
I am not sure from which objet you need last modified date assuming the last modified date from AccOppty__C.
Unfortunatelly this dose not have a direct solution. However, I hope the following code might resolve your probles.
Please note that the code might need little bit twiking as i did not complied it. 
list<AccOppty__c> accOppList = [SELECT ID, Account__c, Opportunity__c, LastmodifiedDate 
									FROM AccOppty__c 
									WHERE Account__c IN: <set of Accounts>];


Map<String, AccOppty__c> result = new list<String, AccOppty__c>();

map<String, list<accOpttWrapper>> tempMap = new map<String, list<accOpttWrapper>> ();
									
for(AccOppty__c aO : result){
	String tempKey = aO.Account__c+'--'+aO.Opportunity__c;
	if(!tempMap.containsKey(tempkey)){
		tempMap.put(tempKey, new list<accOpttWrapper>());
	}
	tempMap.get(tempKey).add(new accOpttWrapper(aO));
}

for(String s : tempMap.keySet()){
	tempMap.get(s).sort();
	result.put(s, null);
	if(tempMap.get(s)!= null && tempMap.get(s).size() > 0){
		result.put(s, tempMap.get(s)[0]);
	}
}


public class accOpttWrapper {
	public AccOppty__c accOpp;
	private integer lMDValue;
	public accOpttWrapper(AccOppty__c tempAccOpp){
		this.accOpp = tempAccOpp;
		this.lMDValue = this.accOpp.lastmodifieddate.getTime();
	}
	
	public integer compareTo(Object compareTo) {
		AccOppty__c compareToEmp = (AccOppty__c)compareTo;
		if (this.lMDValue == compareToEmp.lMDValue) 
			return 0;
		if (this.lMDValue < compareToEmp.lMDValue) 
			return 1;
			
		return -1;        
	}
}