How can I write an SQL query to find 3rd highest salary in a Employees table?

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

            }

        }

    }

}