How to find second or Nth Maximum Salary from Salary Table

Welcome to Quickpickdeal! In this post series, we will delve into the most commonly asked SQL Server interview questions and provide comprehensive answers.

If you've recently attended an interview and have a question you'd like answered, please don't hesitate to leave it as a comment.

In this post, we'll tackle the task of finding the Nth highest salary in SQL Server. This is a frequently asked question in interviews.

It's important to note that there are multiple methods for finding the Nth highest salary. In this post, we'll explore a few of these methods so that by the end, you'll be equipped to answer all these questions:

• How to find the Nth maximum salary from a salary table using a subquery.
• How to find the Nth maximum salary from a salary table using a Common Table Expression (CTE).
• How to find the third, second highest, or 15th highest salary in the employee table (TblEmployeeSalary).

Let's get started!

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

If someone asks us to find the highest salary, it's straightforward. We use the MAX function.

So the highest salary is $85,000. If we want the highest salary, we simply execute the query: SELECT MAX(EmployeeSalary) FROM TblEmployeeSalary, which would give us $85,000.

Select Max(EmployeeSalary) from TblEmployeeSalary

If someone asks us to retrieve the second highest salary, we need to use the MAX function with an outer query as well. So now, when we execute this query, we should get the second highest salary, which is $80,000.

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

Now, this can be used to retrieve any of the highest salaries. For example, if you want the third-highest salary:

1. The highest salary is $85,000.
2. The second-highest salary is $80,000.
3. The third-highest salary is $78,000.

So, if you want the third-highest salary, simply replace N with 3 in the query. When we execute this now, we should get $78,000 as the output. You can use this query to find any nth highest salary.

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 achieve this using a Common Table Expression (CTE) along with the DENSE_RANK SQL server function. The DENSE_RANK function ranks rows and assigns a rank to each row based on the specified criteria.

When using the DENSE_RANK function, we need to specify the result set over which we want the ranking to be applied. In this case, we want to order the result set by the salary column in descending order.

We have now seen how to retrieve the nth highest salary using both a subquery and a CTE.

If you need to find the 15th or 20th highest salary, you can simply replace the "N" value with 15 or 20 accordingly, and the query will still work as expected. So if an interviewer asks you to find any of the highest salaries, you will know what to do.

Point to Be Noted

In many forums, you may come across the following SQL query to retrieve the nth highest salary from the employee table. However, it's important to note that this SQL query will only work correctly if there are no duplicate 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 consider an example illustrated in the table below. Adrian and Colton both have the same salary, which is 85000. Now, I aim to determine the second-highest salary from the table using the query provided above.

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 query below, it will return 85000. However, as observed in the table, the second-highest salary is 80000.