How to Find Second Highest Salary in SQL Server?

Finding the second highest salary within a SQL Server database is very common yet vital work. This query is the backbone for managing payrolls and other databases, analysing data, and preparing reports. SQL stands for Structured Query Language. It is a language that allows retrieving information stored in a database and manipulating exciting information. To understand the several ways of finding the second highest salary, we can write our queries correctly and get better practices of database handling.

A Very Brief History of SQL

SQL was developed during the mid-1970s by IBM researchers Donald D. Chamberlin and Raymond F. Boyce. It was an outgrowth of their original NATURAL-language structured query language, first offered to customers in 1974. It began under the name SEQUEL, short for Structured English Query Language. With time, SQL has grown into a crucial language regarding relational database management systems; at present, it is widely used in most fields and sectors where databases are present.

Before we delve deep into finding the second-highest salary, we require setting up our database. That means creating a table and filling it with some data now.

Defining the Table Structure

Let’s first create an employee table. Well, this table will have employee ID, employee name, and salary columns. Below is an example script for creating the table:

In this script, we define a table named “Employees” with three columns:

• ID: An integer that uniquely identifies each employee.
• Name: A variable character field to store the employee’s name.
• Salary: An integer to store the employee’s salary.

Inserting Data into the Table

Well, now we have the structure of the table. We will have to insert a few data records to make the queries and get some valuable results. Let’s now take an example of a script for inserting some employee records:

In this example, we insert five records into the Employees table. Each record includes an ID, a name, and a salary.

Ensuring Data Accuracy

It’s important to ensure the data we insert is accurate and consistent. For example, each employee should have a unique ID, and salary is an integer with positive values. Ensuring data integrity will avoid errors and produce trustworthy query results.

The Final Data Sheet

Here is the data in our Employees table:

This data set provides a good mix of salaries, enabling us to explore various SQL queries effectively.

Viewing the Data

To check the data we’ve entered, we can use a simple SELECT query to examine all records in the Employees table:

Running this query will display all of the employee records in the table, letting us verify that the data was accurately entered.

Methods to Find the Second Highest Salary in SQL Server

Now that our table is completed, we can proceed toward finding the second-highest salary. We can have several ways to perform this, and each of them has its benefits and different use cases.

Using the Subquery Method

One relatively simple common approach to finding the second highest salary is by using a subquery. This method refers to selecting the maximum salary that is less than the highest salary. Here is how to do it:

Output:

Explanation of the above query:

• The inner subquery returns the highest salary in our table, which is 9000.
• Then, the outer query returns the maximum one less than the highest. The maximum one less than 9000 is 8500.

Using the LIMIT and OFFSET Method

Another way is to employ the LIMIT and OFFSET clauses, which are more prevalent in MySQL but may be converted for SQL Server with identical reasoning. Here’s an example:

Output:

This technique ranks each salary using a window function (DENSE_RANK). The outer query then chooses the salary with a rank of two, the second highest.

Implementing Common Table Expressions (CTEs) for Salary Queries

Common Table Expressions (CTEs) are a way to make a complex query easy. Using a CTE, we can break our query and easily maintain it. Here’s an example: 

Output:

In the above query:

• The CTE SalaryCTE assesses a row number to each salary in descending order.
• The main query selects the salary with a row number of 2, the second highest.

Using the TOP Keyword

SQL Server provides the TOP keyword, which can be used with the DISTINCT keyword to find the second-highest salary. Here’s an example:

Output:

In the above query:

• The inner query provides the highest two unique salaries in descending order. These are 9000 and 8500.
• The outer query then selects the highest one among these, ordered by Salary in ascending order. This will be 8500.

Using the ROW_NUMBER() Function

Now, let’s see how we find the second-highest salary using the ROW_NUMBER() function. This function assigns a unique row number within a result set. Here is how it goes:

Output:

In the given query:

• In the inner subquery, it assigns a row number to each salary, by order in descending salary.
• The outer query selects the salary of row number 2, which happens to be 8500.

Summary of Methods

To summarise, here are the methods we have discussed to find the second highest salary in SQL Server:

1. Subquery Method: Here, nested queries are used.
2. LIMIT and OFFSET Method: This method uses window functions to rank salaries.
3. Common Table Expressions (CTEs): Used to break a complex query into manageable parts.
4. TOP Keyword Method: Used by combining the TOP and DISTINCT keywords to get the desired result.
5. ROW_NUMBER() Function Method: Used for assigning row numbers to rank salaries.

All these methods have their own pros and can be applied according to the situation. So only when we are aware of all the above techniques we can decide about the best method for applying it to our requirements.

Comparing Different Approaches for Efficiency and Simplicity

Choosing the proper method to find the second-highest salary in SQL Server depends on efficiency, simplicity, and specific use-case requirements. Now let us compare the subquery method with the other techniques :

Subquery Method

Efficiency

• The subquery method is very easy to understand.
• It gives acceptable performance for small to medium datasets.
• The nested query might degrade performance with larger datasets.

Simplicity:

• Easy to write and understand.
• Good for small tasks and beginner SQL users.

LIMIT and OFFSET Method

Efficiency:

• Highly efficient with indexed datasets.
• Efficient for large datasets because of its ability to skip rows.
• Performance can vary according to the SQL Server version and configuration.

Simplicity:

• Slightly more complex, as it uses window functions.

• Appropriate for users already familiar with ranking functions.

Common Table Expressions (CTEs)

Efficiency:

• Clear, readable, and very flexible in writing complex queries
• Efficiency is good because CTEs have been optimised for recursive queries or multiple references of the same subquery

Simplicity:

• A bit more advanced than simple subqueries but very handy for making complex queries.
• Best suitable for experienced users who deal with advanced SQL techniques.

TOP Keyword Method

Efficiency:

• Very efficient for fetching fewer rows.
• Can be efficient with indexed columns.
• Best for a large data set when only a couple of the rows are required to be fetched.

Simplicity:

• Easy to write and understand
• Great for quick queries and commonly used scenarios.

ROW_NUMBER() Function Method

Efficiency:

• Effective for datasets where precision in row ranking is needed.
• Generally good in performance with indexed datasets.
• Can operate with large datasets efficiently.

Simplicity:

• Can be marginally complex for users implementing the ROW_NUMBER() and sub-queries.
• It is best suited for users who want precise control of row ranking.

Conclusion

Since getting the second highest salary in SQL Server is a routine task, you will find the methods to do so in numbers. Then, we can learn and select the most practical method that fits our needs at that time. Each method has its good side, either in terms of simplicity, efficiency, or handling large datasets.

The mastery of these techniques aids not only in the SQL queries but also deepens our general understanding of the management of databases. On the whole, efficient querying leads to more performance and more meaning from the data analysis. By doing these types of practices, we can become proficient in SQL and can handle many kinds of data-related operations.