// This will give you all opportunity amount records with descending order and all null values( of amount) will come at end of the list.
List<Opportunity> lstOpportunities = [SELECT Amount FROM Opportunity ORDER BY Amount DESC NULLS LAST];
Set<Decimal> setAmounts = new Set<Decimal>();
List<Decimal> lstAmounts = new List<Decimal>();
// Added values in set to avoid duplicate values
for(Opportunity objOpportunity : lstOpportunities){
setAmounts.add(objOpportunity.amount);
}
lstAmounts.addAll(setAmounts);
System.debug('Highest Amount: '+lstAmounts[0] );
System.debug('2nd Highest Amount: '+lstAmounts[1] );
SELECT query can give wrong result if there the duplicate values in Salary field and you cannot use group by clause as well as groupable flag is always false for number and currency type.
We can simply use this kind of query. This will give second highest amount SELECT Amount FROM Opportunity ORDER BY Amount DESC NULLS LAST limit 1 offset 1
Explaination 1. I am Ordering by amount is DESC order so it will give from largest to lowest 2. NULL LAST will give all the null value in the last 3. Limit 1 means getting only 1 record 4. Offset is basically the index so the highest value will be on 0 index, we want second highest value so I will set it to 1st index by writting offset 1
Let u sknow if this will help you
SELECT Amount FROM Opportunity ORDER BY Amount DESC NULLS LAST limit 1 offset 1
Explaination
1. I am Ordering by amount is DESC order so it will give from largest to lowest
2. NULL LAST will give all the null value in the last
3. Limit 1 means getting only 1 record
4. Offset is basically the index so the highest value will be on 0 index, we want second highest value so I will set it to 1st index by writting offset 1