how to add lastmodifieddate in aggregate list
If you want the 5 most recent Account
records:
- Remove the comma (
,
) after theName
field in yourSELECT
clause. - Remove the
GROUP BY
clause entirely if you want individual records. - In tandem with the above, assign to a
List<Account>
instead ofList<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
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.