Select Second Highest Salary in SQL Server

In SQL Server, you can select the second highest salary using various methods. This is useful when you want to retrieve the second highest value from a column.

Method 1: Using Subquery and MAX

You can use a subquery to select the maximum salary, and then select the maximum salary from the remaining rows.

SELECT MAX(Salary) AS SecondHighestSalary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

This will return the second highest salary from the `Employees` table.

Method 2: Using ROW_NUMBER()

You can use the `ROW_NUMBER()` function to assign a row number to each row, and then select the row with the second highest salary.

WITH RankedSalaries AS ( SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum FROM Employees ) SELECT Salary AS SecondHighestSalary FROM RankedSalaries WHERE RowNum = 2;

This will return the second highest salary from the `Employees` table.

Method 3: Using OFFSET and FETCH

You can use the `OFFSET` and `FETCH` clauses to skip the first row and retrieve the second row.

SELECT Salary AS SecondHighestSalary FROM Employees ORDER BY Salary DESC OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

This will return the second highest salary from the `Employees` table.

Example: Selecting Second Highest Salary from a Table

Let's say we have a table called `Employees` with a `Salary` column, and we want to select the second highest salary.

SELECT MAX(Salary) AS SecondHighestSalary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

This will return the second highest salary from the `Employees` table.

Important Notes

When selecting the second highest salary in SQL Server, keep in mind:

  • The `ROW_NUMBER()` function is only available in SQL Server 2005 and later versions.
  • The `OFFSET` and `FETCH` clauses are only available in SQL Server 2012 and later versions.
  • Be careful when using subqueries, as they can affect the performance of your queries.