function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
KMK91KMK91 

how to write soql query to get 2nd highest salary ?

Amit Yadav 9Amit Yadav 9
You can always query top 2 records and then filter out the least salary record from those two.
Amit Chaudhary 8Amit Chaudhary 8
Please try below code
AggregateResult[] groupedResults = [ SELECT max(Amount) FROM Opportunity ];
Decimal  maxSalary;
for (AggregateResult ar : groupedResults)  
{
   maxSalary = (Decimal )ar.get('expr0');
}

AggregateResult[] groupedResultsRec = [SELECT max(Amount) FROM Opportunity WHERE Amount != :maxSalary ];

Decimal max2;

for (AggregateResult ar : groupedResultsRec )  
{
   max2= (Decimal )ar.get('expr0');
}
System.debug('------------>'+max2);

Let u sknow if this will help you

 
Satyajeet JaragSatyajeet Jarag
Please try with this - 
// 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] );
Mark this as a best if you like ...!

 
selvendirapandi Pselvendirapandi P
select salary from contact order by salary desc limit 1  offset 1
Saravanan Devadass 7Saravanan Devadass 7
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.
smriti sharan19smriti sharan19
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