how to add lastmodifieddate in aggregate list

Question

AggregateResult[] groupedResults;
if(Schema.Account.getSObjectType().getDescribe().isAccessible())

  groupedResults = [SELECT Name,  FROM Account where Name like :sql or name IN:sql1 Group By Name Order By LastModifiedDate Desc limit 5];

Above my query is throwing error,that LastModifiedDate can't be grouped. But I want to display the 5 records which are be modified recently.

Answer 1

If you do need to group the Accounts by name for some reason, and you only want to do this for the 5 accounts that were most recently modified, I think you can use one of the following two options:

Two Queries

List<Account> recentlyModified = [
    SELECT ID 
    FROM Account 
    WHERE Name LIKE :sql OR name IN:sql1
    ORDER BY LastModifiedDate DESC LIMIT 5
    ];

AggregateResult[] groupedResults = [
    SELECT Name 
    FROM Account 
    WHERE Id IN :recentlyModified
    GROUP BY Name 
    ];

1 Query and a Map

List<Account> recentlyModified = [
    SELECT Name
    FROM Account 
    WHERE Name LIKE :sql OR name IN:sql1
    ORDER BY LastModifiedDate DESC LIMIT 5
    ];

//now group the Accounts by Name in a Map
Map<String, List<Account>> groupedResults = new Map<String, List<Account>();
for(Account acc : recentlyModified){
    if(groupedResults.containsKey(acc.Name)){
        groupedResults.get(acc.Name).add(acc);
    }else{
        groupedResults.put(acc.Name, new Account[]{acc});
    }
}   

The above answer by Adrian Larson is correct and I voted for it, but I was in the middle of writing mine, so I figured I'd post it nonetheless as it gives a slightly different approach which can be helpful.

Answer 2

If you want the 5 most recent Account records:

• Remove the comma (,) after the Name field in your SELECT clause.
• Remove the GROUP BY clause entirely if you want individual records.
• In tandem with the above, assign to a List<Account> instead of List<AggregateResult>.

So change it to:

List<Account> mostRecent = [
    SELECT Name FROM Account
    WHERE Name like :sql or name IN:sql1
    ORDER BY LastModifiedDate DESC
    LIMIT 5
];

Notice you don't have to declare and assign the list separately. Better to do both in the same line when you can.

One more note. You want to avoid OR clauses if at all possible in your queries. They tend to run inefficiently. Add sql to the sql1 collection and just filter on that.

sql1.add(sql);
// then inside your query: 
    ... WHERE Name IN :sql1