2

the below query works but it does'nt what I'm expecting and the problem with the below query is that it gives me all the record where as; what I want is to get the minimum dob with Id.

here is my apex code:

List<AggregateResult> aggregateResults = [SELECT id, min(dob__c) dob from myobject__c WHERE Id IN :setIds group by id];

    for (AggregateResult ar : aggregateResults) {
        System.debug(ar.get('dob'));
        System.debug(ar.get('id'));
    }

Should need to re-write in a different way to get the Id & minimum date of birth?

2
  • Aggregate functions don't work that way. They give you the value you request, but are unable to pinpoint the exact row in the database that produced that value. That's done by design, because what Id would you return if you included max(dob__c) in your query as well? The record that contains the min value is not necessarily the same record that contains the max value.
    – Derek F
    Jul 25, 2017 at 19:49
  • If you're asking for a workaround, it would be helpful if you included what you're trying to do with this data. Why do you need both the minimum date of birth and the id?
    – Derek F
    Jul 25, 2017 at 19:51

1 Answer 1

5

In this specific case, it'd be a lot easier to order by date ascending and limit by 1:

MyObject__c minDOBRecord = [SELECT Id FROM MyObject__c WHERE Id = :setIds ORDER BY DOB__c ASC LIMIT 1];

In real SQL, it'd be easier to do this with a join, but we can't do that in SOQL, so this is the most efficient way to do this.

The GROUP BY option doesn't limit how many results you get back, so you're basically asking for each record to be grouped by itself.

AggregateResult is primarily used when you don't care about a specific record, but you need to know a specific value (e.g. the minimum DOB out of all records in the set).

In other words, AggregateResult can answer "what's the oldest DOB?", but it can't answer "which record has the oldest DOB?".

1
  • I don't know why I didn't think of this approach and definitely lot easier :) 1+
    – Nick Kahn
    Jul 25, 2017 at 20:13

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.