SQL Most Commonly Asked Interview Question | 5 ways to Solve it.
One of the most commonly asked SQL interview questions is, Find the 2nd/Nth highest salary from a table. In this article, I will show you 5 different ways to solve this.
Method 1: Using Subquery (2nd highest only)
Consider the following Employee table with Emp_ID, Name, and Salary as the columns. Notice that the salaries are not unique and are repeated.
If you just want to find out the 2nd highest salary, the below code does the job
Method 2: Using Subquery (2nd highest only)
This method is similar to the previous one, the only change is that the ‘not in’ operator is used here.
Method 3: Using Correlated Subquery(Nth Highest)
A correlated subquery is the one that uses the values of the outer query, i.e. the inner query is dependent on the outer query.
Using this method you can find out 2nd, 3rd, 5th, 15th or any Nth salary you wish to find out
Make sure you replace the value of N to the desired salary position, If you want the 2nd highest salary then replace N =2, 4th highest salary then replace N =4, 10th highest salary then replace N =10, and so on.
Method 4: Using Subquery(Nth highest)
This method is similar to Method 2 but this is not just restricted to the 2nd highest salary, the below query can be used to find out the Nth highest salary.
Make sure you replace the value of N to the desired salary position, If you want the 2nd highest salary then replace N =2, 4th highest salary then replace N =4, 10th highest salary then replace N =10, and so on.
Method 5: Using Common Table Expressions CTE(Nth highest Salary)
A CTE stores a temporary result set that can be used within a Select, Insert, Update, or Delete statement.
The below query is used to fetch Nth highest salary using the CTE.
Make sure you replace the value of N to the desired salary position, If you want the 2nd highest salary then replace N =2, 4th highest salary then replace N =4, 10th highest salary then replace N =10, and so on.
Conclusion
These were the five different ways to solve the 2nd/Nth highest salary problem in SQL. I hope this helps.
