In this article, we are going to discuss four ways to select second highest salary in MS SQL. This is a very commonly asked interview question for sql.
Who is the audience
Anyone who works on a “Microsoft SQL server” may be a developer, Tester, and DBA many more. Because, I will be using some inbuilt functions of MS SQL, which might not be available in other database tools like Oracle, My SQL, PostgreSQL etc.
Don’t get me wrong, For sure other database tools might have these functions, but with different name and syntax.
Let’s jump to the solutions
I have created one table called Employee with three columns Id, Name, and Salary, We will be using the same table for all solutions.
As you can see in the above table, Employee with name D takes the second-highest salary among all.
Using Max() function
The MAX() is an MS SQL inbuild function can be used for returning the largest value of the selected column.
Select only salary column
SELECT Max(salary) AS Salary FROM employee WHERE salary < (SELECT Max(salary) FROM employee)
Select salary column along with Id and Name
SELECT TOP 1 id, NAME, Max(salary) FROM employee WHERE salary < (SELECT Max(salary) FROM employee) GROUP BY id, NAME, salary ORDER BY salary DESC
Using Inline SQL query
We can use an inline query to select the second highest or nth highest salary in the table. You just have to replace the number in the inline query.
SELECT TOP 1 * FROM (SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary DESC) AS t ORDER BY t.salary ASC
You can just put 3 in the inline query, In case you want to select the 3rd highest salary.
Using Dense_Rank(), MS SQL inbuilt function & Inline query
DENSE_RANK gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.
select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as Rank from Employee
Using Dense_Rank() function we can assign a rank to the salary column order by desc. As you can see in the below output E got 1st rank, D got rank 2, but B and F got the same rank because both the employees have the same salary.
In order to get the 2nd highest salary you just need to keep above query as an inline query and place the where condition with Rank=2
select top 1 Id,Name,Salary from ( select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as R from Employee) result where result.R=2
Using Dense_Rank(), MS SQL inbuilt function & CTE
CTE or common table expression is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
We are using the same Dense_Rank() query used in the previous solution. This time we will be using CTE instead of an inline query.
with tmp_emp as( select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as Rank from Employee)
Above query will keep the result in the temp result set using CTE.
Now, we just need to use a tmp_emp table with where clause and number to get the nth highest salary.
select Id,Name,Salary from tmp_emp where tmp_emp.Rank=2