+ Start a Discussion
Sonu06Sonu06 

How to Find Second Maximum value of Number field??

Hey guys , I am beginner to salesforce, my reqirement is I have to find the Second Maximum amount of oppurtunity using Soql query,, so what will be the query ??
Best Answer chosen by Sonu06
MKRMKR
Hi,

You can achieve this with order by, limit and offset.
 
List<Opportunity> secondHighestAmount = [SELECT Id, Name, Amount FROM Opportunity WHERE <your_where_clause> ORDER BY Amount DESC NULLS LAST LIMIT 1 OFFSET 1];
if(secondHighestAmount.size() == 1) {
     Opportunity secondHighestOpp = secondHighestAmount.get(0);
     //Do something with secondHighestOpp
}
Explanation:
  • ORDER BY Amount DESC NULLS LAST (get the opportunities in the amount order from highest to lowest)
  • LIMIT 1 (get only one record)
  • OFFSET 1 (skip the highest amount opportunity)
Regards,
MKR

All Answers

ghd sport 2ghd sport 2
Hii .. mr sonal.... follow below guide to solve your problem -  https://developer.salesforce.com/platform - best regards - ghd sports app (https://ghdsportsapk.com/)
MKRMKR
Hi,

You can achieve this with order by, limit and offset.
 
List<Opportunity> secondHighestAmount = [SELECT Id, Name, Amount FROM Opportunity WHERE <your_where_clause> ORDER BY Amount DESC NULLS LAST LIMIT 1 OFFSET 1];
if(secondHighestAmount.size() == 1) {
     Opportunity secondHighestOpp = secondHighestAmount.get(0);
     //Do something with secondHighestOpp
}
Explanation:
  • ORDER BY Amount DESC NULLS LAST (get the opportunities in the amount order from highest to lowest)
  • LIMIT 1 (get only one record)
  • OFFSET 1 (skip the highest amount opportunity)
Regards,
MKR
This was selected as the best answer
Dushyant SonwarDushyant Sonwar
Hi Sonal ,

Didn't understand your problem , are you saying you want to query the second opportunity amount , then you can use the order by clause to sort  and get the second record or opportunity.

list<Opportunity> lstOpportunity = [Select amount from opportunity order by opportunity desc];