[SalesForce] get MIN date with Id from aggregate result query

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?

Best Answer

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

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

[SalesForce] AggregateResult for a multi-select picklist

https://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/Content/sforce_api_calls_soql_select_agg_functions_field_types.htm

As per the document link above there is no way to use AggregateResult

However if the no of records are less than 50000 rows you can still loop over the records and use Maps to seggregate and do the count

If you are building something like reporting use @ReadOnly attribute on page so that the no of Rows retrieved is increased .

Best Answer

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

[SalesForce] AggregateResult for a multi-select picklist

Related Topic