How to find second or Nth maximum salary from salary table

Welcome to Appsloveworld in this post series, we discuss the most commonly asked SQL server interview questions and answers.

If you have attended an interview and if you have a question to be answered, please feel free to leave that question as a comment.

In this post, We’ll discuss how to find Nth high salary in the SQL server. This one of the very commonly asked questions in the Interview question.

Keep in mind, there are several ways of finding the Nth highest salary. In this post, we will discuss a few of those, by the end of this post, will be able to answer all these questions.

• How to find the Nth maximum salary from salary table using a subquery.
• How to find the Nth maximum salary from salary table using using CTE.
• How to find third, second highest, or 15th highest salary in emp table.

We will be using TblEmployeeSalary employee table

Table Script

CREATE TABLE [dbo].[TblEmployeeSalary](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [nvarchar](max) NULL,
    [EmployeeSalary] [float] NULL,
    [Adress] [nvarchar](max) NULL,
 CONSTRAINT [PK_TblEmployeeSalary] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeSalary] ON 
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (1, 'Hunter', 80000, 'Osaka')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (2, 'Connor', 70000, 'Kyoto')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (3, 'Ezra', 60000, 'Vienna, Austria')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (4, 'Aaron', 55000, 'Tokyo')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (5, 'Adrian', 85000, 'Amsterdam, Netherlands')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (6, 'Easton', 72000, 'Barcelona, Spain')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (7, 'Colton', 85000, 'Singapore')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (8, 'Angel', 42000, 'Monte Carlo, Monaco')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (9, 'Jaxson', 32000, 'Cologne, Germany')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (10, 'Greyson', 45000, 'Amsterdam, Netherlands')
GO
INSERT [dbo].[TblEmployeeSalary] ([Id], [EmployeeName], [EmployeeSalary], [Adress]) VALUES (11, 'Brayden', 78000, 'Tokyo')
GO

Now, if somebody asks us to find the highest salary that straightforward, all we do is use the max function.

So the higher salary is 85000. if we want the highest salary, we simply say Select Max(EmployeeSalary) from TblEmployeeSalary that would give us 85000.

Select Max(EmployeeSalary) from TblEmployeeSalary

But if somebody asks us to retrieve the second highest salary, then how are we going to do that?
But that’s not what we want.

We want the second-highest salary,  To get the second highest we are going to use the max function with the outer query as well. So now when we execute this, we should get the second highest salary, which is 80000.

Select Max(EmployeeSalary) from TblEmployeeSalary 
where EmployeeSalary < (Select Max(EmployeeSalary) 
from TblEmployeeSalary)

Now let’s see how to get and nth highest salary. First, let’s see how to get nth higher salary using a subquery

How to find nth highest salary using Sub-Query

Generic Query

SELECT TOP 1 EmployeeSalary
FROM (
SELECT DISTINCT TOP Nth EmployeeSalary
FROM Table_Name
ORDER BY Table_Name DESC
) QueryRESULT
ORDER BY EmployeeSalary

So now this clearly can be used to retire any of the highest salaries, if you want. Let’s say the third-highest salary.

So what is the third-highest salary here? The first is 85000, the second is 80000, the third is 78000. So if you want the third-highest salary, simply replace N with 3.

So when we execute this now, we should get 78000 as the output. so you can use this query to find any of the nth highest salaries.

SELECT TOP 1 EmployeeSalary
FROM (
SELECT DISTINCT TOP 3 EmployeeSalary
FROM TblEmployeeSalary
ORDER BY EmployeeSalary DESC
) QueryRESULT
ORDER BY EmployeeSalary

How To find nth highest salary using CTE

Generic Query

WITH QueryRESULT AS
(
SELECT EmployeeSalary,
DENSE_RANK() OVER (ORDER BY EmployeeSalary DESC) AS DENSERANK
FROM Table_Name
)
SELECT TOP 1 EmployeeSalary
FROM QueryRESULT
WHERE DENSERANK = Nth

Now if we want the second-highest salary I put to 2. So now when we execute this, we should get 80000, which is our second highest.

WITH QueryRESULT AS
(
SELECT EmployeeSalary,
DENSE_RANK() OVER (ORDER BY EmployeeSalary DESC) AS DENSERANK
FROM TblEmployeeSalary
)
SELECT TOP 1 EmployeeSalary
FROM QueryRESULT
WHERE DENSERANK = 2

Now let’s see how to do it using CTE stands for Common Table Expression. I’m going to use the DENSE_RANK SQL server function here and a bit you’ll understand what the DENSE_RANK function is going to do. Now, it’s going to obviously rank rows and then give in rank to every row.

So when we use the DENSE_RANK function, we’ll have to specify the result set upon which we want that DENSE_RANK to be applied. we want the DENSE_RANK to be applied over the results set that we are going to specify here. So what we want to do is we want to order by salary column and that in descending order.

All right. So we have seen how to retrieve an nth highest salary using a subquery,using CTE as well.

Now, obviously, you know, this is going to be in here, meaning if you want 15th or 20th highest salary, simply replace it with 15 or 20 and it’s simply going to work. so if an interviewer asks you to find any of the highest salaries, you know what to do.

Point to Be Noted

On many of the forums,you can see the below SQL query to get the nth highest salary from the employee table. But let me tell you that this SQL query will only work if there are no duplicates salary records in the table.

WITH QueryRESULT AS
(
SELECT EmployeeSalary,
ROW_NUMBER() OVER (ORDER BY EmployeeSalary DESC) AS ROWNUMBER
FROM TblEmployeeSalary
)
SELECT EmployeeSalary
FROM QueryRESULT
WHERE ROWNUMBER = 1

Let’s take an example as you can see in the below table image Adrian and Colton have the same salary i. e 85000. Now I want to find out the second-highest salary from the table using the above query.

WITH QueryRESULT AS
(
SELECT EmployeeSalary,
ROW_NUMBER() OVER (ORDER BY EmployeeSalary DESC) AS ROWNUMBER
FROM TblEmployeeSalary
)
SELECT EmployeeSalary
FROM QueryRESULT
WHERE ROWNUMBER = 2

Now execute the below query it will return the 85000 but as you can see in the table 2nd highest salary is 80000.

The post How to find second or Nth maximum salary from salary table appeared first on Software Development | Programming Tutorials.