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();
}
}
}
}