SQL query to find second highest salary?

SQL query to find second highest salary?

To find the second highest salary from a table, you can use different methods based on sub-queries, the ORDER BY clause, or even database-specific functions. Here are some of the common methods:

1. Using a Sub-query:

You can retrieve the second highest salary by filtering out the maximum salary and then finding the maximum of what's left.

SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees);

2. Using the ORDER BY and LIMIT:

If your RDBMS supports LIMIT and OFFSET (like MySQL, PostgreSQL), you can order the salaries in descending order and then skip the highest one.

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

3. Using the ORDER BY with the ROWNUM or ROW_NUMBER():

In databases like Oracle, you can use the ROWNUM pseudo-column or the ROW_NUMBER() window function:

Using ROWNUM:

SELECT salary FROM ( SELECT salary, ROWNUM as rank FROM ( SELECT DISTINCT salary FROM employees ORDER BY salary DESC ) WHERE ROWNUM <= 2 ) WHERE rank = 2;

Using ROW_NUMBER():

SELECT salary FROM ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees ) WHERE rank = 2;

4. Using Database Specific Functions:

Some databases have specific functions or methods to handle such use cases. For instance, in MySQL, you can use the DISTINCT keyword with ORDER BY and LIMIT:

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1, 1;

While all the above methods achieve the same result, the best method can vary depending on the specific database system, the volume of data, indexing, and other factors. When dealing with real-world scenarios, it's essential to consider performance and choose the most efficient method for your specific database setup.

Examples

  1. SQL query to find second highest salary in a table:

    • Using the ORDER BY clause in descending order and limiting the result set to one row.
    SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

  2. Finding the second highest salary using SQL:

    • Similar to the first example, ordering the salaries in descending order and selecting the second row.
    SELECT salary AS second_highest_salary FROM employees ORDER BY salary DESC OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

  3. SQL SELECT statement for second highest salary:

    • Utilizing the LIMIT clause to restrict the result to the second row after ordering by salary.
    SELECT salary AS second_highest_salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

  4. How to retrieve the second highest salary in SQL:

    • Using a subquery to exclude the highest salary before finding the maximum.
    SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

  5. Finding the runner-up salary in SQL:

    • Similar to the first example, finding the maximum salary excluding the highest one.
    SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

  6. Query to get the second highest salary in a database:

    • Using LIMIT to get the second highest salary after ordering by salary in descending order.
    SELECT salary AS second_highest_salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

  7. Using ORDER BY and LIMIT for second highest salary in SQL:

    • Ordering the salaries in descending order and limiting the result set to the second row.
    SELECT salary AS second_highest_salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

  8. SQL subquery to find second highest salary:

    • Using a subquery to find the maximum salary excluding the highest one.
    SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

  9. Ranking and partitioning for second highest salary in SQL:

    • Utilizing the RANK() window function to rank salaries and selecting the second-ranked salary.
    SELECT salary AS second_highest_salary FROM ( SELECT salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees ) ranked_salaries WHERE salary_rank = 2;

  10. SQL query to exclude highest salary and find the second highest:

    • Using NOT IN to exclude the highest salary and then finding the maximum.
    SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees);

More Tags

code-coverage date-comparison google-api pandas-groupby stringr post random-forest google-api-python-client arcgis prefix

More Programming Guides

Other Guides

More Programming Examples