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
sp13sp13 

query all account ordered by latest contact

I have a field TotalContacts__c which has the total numbers of contacts of each account. I need to query all contacts ordered by the total number of contacts [SELECT Name FROM Account ORDER BY TotalContacts__c DESC] but I also need to order them by which has the latest contact.

For example, if Account 1, Account 2 and Account 3 all has 5 TotalContacts__c, then I need them to be ordered by which one has the lastest related contact.
 example:
Account Name   TOtalContacts__c   (last contact related to account is created on:
Account 1                       5                            08/20/2015
Account 3                       5                            08/18/2015
Account 2                       5                            07/30/2015

 
ClintLeeClintLee
I don't believe you can do this in a single query.

You could use a wrapper class with a custom sort method using the Comparable interface.  Then, query your Accounts and child Contact records and feed them to the wrapper class.

Wrapper Class:
 
public class AccountWrapper implements Comparable
{
    public Account acct { get; set; }
    
    public AccountWrapper(acct)
    {
        this.acct = acct;
    }
    
    public Integer compareTo(Object compareTo)
    {
        Account compAccount = (Account)compareTo;
        
        if(acct.Total_Contacts__c > compAccount.Total_Contacts__c) {
            return 1;
        } else if(acct.Total_Contacts__c < compAccount.Total_Contacts__c) {
            return -1;
        } else if(acct.Total_Contacts__c == compAccount.Total_Contacts__c) {
            if(acct.Total_Contacts__c == 0 ) {
                return 0;
            }else if(acct.Contacts[0].CreatedDate > compAccount.Contacts[0].CreatedDate) {
                return 1;
            } else if(acct.Contacts[0].CreatedDate < compAccount.Contacts[0].CreatedDate) {
                return -1;
            } else if(acct.Contacts[0].CreatedDate == compAccount.Contacts[0].CreatedDate) {
                return 0;
            }
        }
    }
}

Query your accounts and custom sort:
 
List<AccountWrapper> wrappedAccounts = new List<Account>();

// query your accounts/contacts and in the contact query sort 
// by CreatedDate DESC so the first contact in the list always 
// has the latest CreatedDate.
for(Account account : [select Id, Total_Contacts__c, (select CreatedDate from Contacts order by CreatedDate DESC) from Account])
{
    AccountWrapper wrappedAccount = new AccountWrapper(account);
    wrappedAccounts.add(wrappedAccount);
}

wrappedAccounts.sort();  // custom sort

// extract your sorted accounts into a list of accounts
List<Account> sortedAccounts = new List<Account>();

for(AccountWrapper wrappedAccount : wrappedAccounts)
{
    sortedAccounts.add(wrappedAccount.account);
}

Hope that helps,

Clint
sp13sp13
@ClintLee it gives me this error: "Variable does not exist: Contacts" Line 21 of the wrapper class
ClintLeeClintLee
I left something out of the constructor.

Line 5 should be 
public AccountWrapper(Account acct)

 
ClintLeeClintLee
This compiles for me.
 
public class AccountWrapper implements Comparable
{
    public Account acct { get; set; }
    
    public AccountWrapper(Account acct)
    {
        this.acct = acct;
    }
    
    public Integer compareTo(Object compareTo)
    {
        Account compAccount = (Account)compareTo;
        
        if(acct.Total_Contacts__c > compAccount.Total_Contacts__c) {
            return 1;
        } else if(acct.Total_Contacts__c < compAccount.Total_Contacts__c) {
            return -1;
        } else if(acct.Total_Contacts__c == compAccount.Total_Contacts__c) {
            if(acct.Total_Contacts__c == 0 ) {
                return 0;
            }else if(acct.Contacts[0].CreatedDate > compAccount.Contacts[0].CreatedDate) {
                return 1;
            } else if(acct.Contacts[0].CreatedDate < compAccount.Contacts[0].CreatedDate) {
                return -1;
            } else if(acct.Contacts[0].CreatedDate == compAccount.Contacts[0].CreatedDate) {
                return 0;
            } else {
                return 0;
            }
        } else {
            return 0;
        }
    }
}

 
sp13sp13
@ClintLee now it gives me this error: System.TypeException: Invalid conversion from runtime type AccountWrapper to Account on Line 12