sql server - 3rd highest salary sql

Sql server - 3rd highest salary sql

To find the third-highest salary in SQL Server, you can use a combination of the DISTINCT, ORDER BY, and TOP clauses. Here's how you can do it:

SELECT DISTINCT Salary FROM ( SELECT DISTINCT TOP 3 Salary FROM Employees ORDER BY Salary DESC ) AS ThirdHighest ORDER BY Salary ASC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

This query first selects the top 3 distinct salaries ordered by descending order. Then, it orders these salaries in ascending order and skips the first two salaries using the OFFSET clause and selects the next 1 row using the FETCH NEXT clause.

Assuming you have a table named Employees with a column Salary, this query will return the third-highest salary from that table. Adjust the table and column names accordingly based on your schema.

Examples

  1. SQL Server 3rd highest salary using TOP and ORDER BY

    • Description: Retrieves the 3rd highest salary from an Employees table using TOP and ORDER BY in SQL Server.
    SELECT TOP 1 Salary FROM ( SELECT DISTINCT TOP 3 Salary FROM Employees ORDER BY Salary DESC ) AS ThirdHighest ORDER BY Salary ASC;

    This query first selects the top 3 distinct salaries in descending order and then selects the lowest salary among these three, effectively giving the 3rd highest salary.

  2. SQL Server 3rd highest salary using OFFSET FETCH

    • Description: Retrieves the 3rd highest salary from an Employees table using OFFSET and FETCH in SQL Server.
    SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

    This query sorts salaries in descending order and skips the first two rows (OFFSET 2 ROWS) before fetching the next row (FETCH NEXT 1 ROWS ONLY), thus retrieving the 3rd highest salary.

  3. SQL Server 3rd highest salary using subquery

    • Description: Retrieves the 3rd highest salary from an Employees table using a subquery in SQL Server.
    SELECT MAX(Salary) AS ThirdHighestSalary FROM Employees WHERE Salary < ( SELECT MAX(Salary) FROM Employees WHERE Salary < ( SELECT MAX(Salary) FROM Employees ) );

    This query uses nested subqueries to find the maximum salary that is less than the maximum salary found excluding the highest salary, effectively retrieving the 3rd highest salary.

  4. SQL Server 3rd highest salary using ROW_NUMBER()

    • Description: Retrieves the 3rd highest salary from an Employees table using ROW_NUMBER() in SQL Server.
    SELECT Salary FROM ( SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum FROM Employees ) AS SalaryRanked WHERE RowNum = 3;

    This query ranks salaries in descending order using ROW_NUMBER() and then filters for the salary where RowNum equals 3, thereby fetching the 3rd highest salary.

  5. SQL Server 3rd highest salary using dense_rank()

    • Description: Retrieves the 3rd highest salary from an Employees table using DENSE_RANK() in SQL Server.
    SELECT DISTINCT Salary FROM ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees ) AS SalaryRanked WHERE Rank = 3;

    This query uses DENSE_RANK() to rank salaries in descending order and then selects the salary where Rank equals 3, providing the 3rd highest salary.

  6. SQL Server 3rd highest salary using CTE

    • Description: Retrieves the 3rd highest salary from an Employees table using a Common Table Expression (CTE) in SQL Server.
    WITH RankedSalaries AS ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees ) SELECT Salary FROM RankedSalaries WHERE Rank = 3;

    This query creates a CTE (RankedSalaries) to compute the ranks of salaries in descending order and then selects the salary where Rank equals 3 from the CTE.

  7. SQL Server 3rd highest salary using TOP 1 with NOT IN

    • Description: Retrieves the 3rd highest salary from an Employees table using TOP with NOT IN in SQL Server.
    SELECT TOP 1 Salary FROM Employees WHERE Salary NOT IN ( SELECT TOP 2 Salary FROM Employees ORDER BY Salary DESC ) ORDER BY Salary DESC;

    This query uses TOP with NOT IN to exclude the top 2 salaries (highest and 2nd highest) and then selects the top salary from the remaining set, which is the 3rd highest salary.

  8. SQL Server 3rd highest salary using CROSS APPLY

    • Description: Retrieves the 3rd highest salary from an Employees table using CROSS APPLY in SQL Server.
    SELECT DISTINCT Salary FROM Employees e1 CROSS APPLY ( SELECT TOP 1 Salary FROM Employees e2 WHERE e2.Salary < e1.Salary ORDER BY e2.Salary DESC ) AS ThirdHighest WHERE ThirdHighest.Salary IS NOT NULL;

    This query uses CROSS APPLY to join the Employees table with a subquery that finds the top 1 salary less than each salary in e1, effectively retrieving the 3rd highest salary.

  9. SQL Server 3rd highest salary using MINUS

    • Description: Retrieves the 3rd highest salary from an Employees table using MINUS (or EXCEPT in SQL Server).
    SELECT TOP 1 Salary FROM Employees EXCEPT SELECT TOP 2 Salary FROM Employees ORDER BY Salary DESC;

    This query uses EXCEPT (or MINUS in other SQL dialects) to exclude the top 2 salaries and then selects the top salary from the remaining set, which is the 3rd highest salary.

  10. SQL Server 3rd highest salary using OUTER APPLY

    • Description: Retrieves the 3rd highest salary from an Employees table using OUTER APPLY in SQL Server.
    SELECT DISTINCT Salary FROM Employees e1 OUTER APPLY ( SELECT TOP 1 Salary FROM Employees e2 WHERE e2.Salary < e1.Salary ORDER BY e2.Salary DESC ) AS ThirdHighest WHERE ThirdHighest.Salary IS NOT NULL ORDER BY Salary DESC;

    This query uses OUTER APPLY to join the Employees table with a subquery that finds the top 1 salary less than each salary in e1, effectively retrieving the 3rd highest salary.

More Tags

multiple-matches x86-64 message-listener mouseclick-event unc datatemplate mysql-8.0 whatsapp listview pagination

More Programming Questions

More Investment Calculators

More Everyday Utility Calculators

More Mixtures and solutions Calculators

More Tax and Salary Calculators