+ Start a Discussion
Ramesh A 8Ramesh A 8 

Unable to get the second highest value.

Hello,

I am trying to get the second highest value in SOQL with below Query,
 
select Max(Age__C) from Patient__c Where Age__C NOT IN ( select MAX(Age__c) from Patient__c )
But am getting result with below Query
 
select Max(Age__C) from Patient__c Where Age__C NOT IN (23)


 
thatheraherethatherahere
Hi Ramesh,

Try below Aggregate Queries:
 
list<AggregateResult> maxResult = [SELECT Max(Age__c) maxVal from Patient__c]; // Get First Max Value. 
System.debug('Max: '+maxResult.get(0).get('maxVal')); 
Integer firstMax = Integer.valueOf( maxResult.get(0).get('maxVal') ); 

list<AggregateResult> secondMaxResult = [SELECT Max(Age__c) maxVal from Patient__c WHERE Age__c NOT IN :new list<Integer>{ firstMax}]; // Get Second Max Value. 
System.debug('Second Max: '+secondMaxResult.get(0).get('maxVal'));


- thatherahere
Pankaj_GanwaniPankaj_Ganwani
Hi Ramesh,

You can try something like this if your Age__c field is of type number:

List<Patient__c> lstPatient = [select Age__c from Patient__c order by Age__c DESC limit 2];
system.debug('============'+lstPatient[1].Age__c);