On Account object I've a text field called 'Group__c'. Let's say we have four records in account, 2 accounts with group = management and 2 accounts with group = marketing.

So, here the logic I would like is to query records with group = management & marketing with one record each (we have 2 records each in database).

Set<String> groups = new Set<String> {'Marketing', 'Management'};

List<Account> accounts = [SELECT Id, Group__c FROM Account WHERE Group__c IN :groups];

The query returns all 4 records, but I need a query to return only 2 records (one management & one marketing).

  • What condition do you have for selecting an account from each group? – Phil W Nov 27 '20 at 7:51
  • i want the query to return account records with unique groups, and exclude the duplicate groups – V Reddy Nov 27 '20 at 7:57
  • That doesn't explain. If I have Account A and Account B both with Group "Marketing", which of A and B should be selected? – Phil W Nov 27 '20 at 7:59
  • Any of A or B is fine – V Reddy Nov 27 '20 at 8:04
  • Any of A or B is fine – V Reddy Nov 27 '20 at 8:04

The go-to SOQL technology in this sort of scenario is to use an Aggregate Query. In this case you want data grouped by Group__c. However, since you still want to get back an Account for each group you must decide the criteria for selecting the account from each group.

To be able to do this in a single aggregate query you will need to restrict that decision based on use of an aggregate function against the Account ID. For example, the following will work:

Set<String> groups = new Set<String> {'Marketing', 'Management'};

List<AggregateResult> results = [SELECT MIN(Id) accountId, Group__c FROM Account GROUP BY Group__c];

This will typically return the account with the earliest CreatedDate in each group due to the way IDs are normally allocated. You might use MAX(Id) instead. If you need something more sophisticated you will typically have to perform two queries; an aggregate followed by an SOQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.