How can I write an SQL query to find 3rd highest salary in a Employees table?
I am a database analyst at a particular company that maintains an employee database. This particular database includes a table which is called “Employees” which has EmployeeID, Name, Department, and Salary file. Now the management has requested me to find the third Highest salary in the company. How can I write an SQL query to approach this particular task?
In the context of DS, Here is how you can approach this particular task:-
Select the distinct salaries
Firstly, you would need to obtain a list of distinct salaries to ensure that the duplicate salary values do not affect the results.
Order the salaries
Now you should sort these salaries in descending order so that you can achieve the highest salary appears first.
Limit the results
Now you can use a limit or row numbering approach to approach to select the third highest value.
Here is a SQL code given of how you can find the third highest salary:-
SELECT
Salary
FROM (
SELECT DISTINCT
Salary
FROM
Employees
ORDER BY
Salary DESC
LIMIT 1 OFFSET 2
) AS ThirdHighestSalary;For the SQL dialects that support Windows function, such as SQL server, Postgresql, or even Oracle then you can alternatively use the ROW_NUMBER to achieve this particular same result:-
SELECT
Salary
FROM (
SELECT
Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM
(SELECT DISTINCT Salary FROM Employees) AS DistinctSalaries
) AS NumberedSalaries
WHERE
RowNum = 3;Here is also a java based example given below:-
Import java.sql.Connection;
Import java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import java.sql.ResultSet;
Import java.sql.SQLException;Public class ThirdHighestSalaryFinder {
// JDBC URL, username, and password of MySQL server
Private static final String URL = “jdbc:mysql://localhost:3306/your_database”;
Private static final String USER = “your_username”;
Private static final String PASSWORD = “your_password”;
// JDBC variables for opening and managing connection
Private static Connection connection;
Public static void main(String[] args) {
Try { // Establish connection to the database Connection = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println(“Database connected successfully.”);
// Call the method to find the third highest salary
Double thirdHighestSalary = findThirdHighestSalary();
If (thirdHighestSalary != null) {
System.out.println(“The third highest salary is: “ + thirdHighestSalary);
} else {
System.out.println(“There are less than three distinct salaries in the Employees table.”);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// Close the connection
If (connection != null) {
Try {
Connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Public static Double findThirdHighestSalary() throws SQLException {
// SQL query to find the third highest salary
String sql = “SELECT Salary FROM (“
+ “ SELECT DISTINCT Salary “
+ “ FROM Employees “
+ “ ORDER BY Salary DESC “
+ “ LIMIT 1 OFFSET 2”
+ “) AS ThirdHighestSalary”;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Try {
// Prepare statement
preparedStatement = connection.prepareStatement(sql);
// Execute query
resultSet = preparedStatement.executeQuery();
// Process the result set
If (resultSet.next()) {
Return resultSet.getDouble(“Salary”);
} else {
Return null;
}
} finally {
// Close the result set and statement
If (resultSet != null) {
resultSet.close();
}
If (preparedStatement != null) {
preparedStatement.close();
}
}
}
}
Answers (2)
To find the 3rd highest salary in an Employees table using SQL, there are multiple approaches. Here are some of the most commonly used methods:
1. Using LIMIT and OFFSET (for MySQL, PostgreSQL)
This is the simplest method:
SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;
- ORDER BY salary DESC sorts salaries in descending order.
- LIMIT 1 OFFSET 2 skips the first two highest salaries and fetches only the third.
2. Using DISTINCT and ORDER BY with LIMIT
Another approach without OFFSET:
SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;
This returns the top 3 salaries, so you need to take the last row.
3. Using a Subquery (Works in Most SQL Databases)
SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees));
- The innermost query finds the highest salary.
- The second query finds the second highest.
- The outermost query gets the third highest.
4. Using DENSE_RANK() (For SQL Server, PostgreSQL, Oracle)
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employees
) ranked
WHERE rnk = 3;
- DENSE_RANK() assigns a unique rank to each distinct salary.
- We filter for rnk = 3 to get the third highest.
Each method has its advantages depending on the database system. If your table has duplicate salaries, DENSE_RANK() is a reliable choice.
Let me know if you need further clarifications!
2 Weeks
@Tomb of the Mask, Thanks for your reply. This is exactly what I was looking for.
3 Months
To find the 3rd highest salary in an Employees table using SQL, there are multiple approaches. Here are some of the most commonly used methods:
1. Using LIMIT and OFFSET (for MySQL, PostgreSQL)
This is the simplest method:
SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;- ORDER BY salary DESC sorts salaries in descending order.
- LIMIT 1 OFFSET 2 skips the first two highest salaries and fetches only the third.
2. Using DISTINCT and ORDER BY with LIMIT
Another approach without OFFSET:
SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;This returns the top 3 salaries, so you need to take the last row.
3. Using a Subquery (Works in Most SQL Databases)
SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees));- The innermost query finds the highest salary.
- The second query finds the second highest.
- The outermost query gets the third highest.
4. Using DENSE_RANK() (For SQL Server, PostgreSQL, Oracle)
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employees
) ranked
WHERE rnk = 3;- DENSE_RANK() assigns a unique rank to each distinct salary.
- We filter for rnk = 3 to get the third highest.
Each method has its advantages depending on the database system. If your table has duplicate salaries, DENSE_RANK() is a reliable choice.
Let me know if you need further clarifications!
@Tomb of the Mask, Thanks for your reply. This is exactly what I was looking for.