get MIN date with Id from aggregate result query

Question

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?

Answer 1

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?".