+ Start a Discussion
Ramesh A 8Ramesh A 8 

Unable to get the second highest value.


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)

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
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];