SQL | How to find Nth highest salary from a table

SQL | How to find Nth highest salary from a table

To find the Nth highest salary from a table, there are several approaches you can use, depending on the specific SQL database system you are using. Here are some methods using standard SQL:

Assuming the table is named employees and the salary column is salary.

  • Using a subquery with the DISTINCT keyword and the LIMIT clause (works for databases that support the LIMIT clause, such as MySQL):
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N-1; -- replace N with the desired value
  • Using the ROW_NUMBER() window function (commonly supported in SQL Server, Oracle, PostgreSQL, etc.):
WITH RankedSalaries AS ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM (SELECT DISTINCT salary FROM employees) AS SubQuery ) SELECT salary FROM RankedSalaries WHERE rank = N; -- replace N with the desired value
  • Using a correlated subquery:
SELECT salary FROM employees e1 WHERE N-1 = (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e2.salary > e1.salary);

Choose the method that best fits your database system and your specific use case. Remember to replace N with the desired rank to get the Nth highest salary.

Examples

  1. Finding the Second Highest Salary in SQL:

    SELECT MAX(Salary) AS SecondHighestSalary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

  2. How to Get the Third Highest Salary Using SQL:

    SELECT MAX(Salary) AS ThirdHighestSalary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees));

  3. SQL Query to Find the Top N Salaries from a Table:

    SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 5; -- Change this to the desired value of N

  4. Using ORDER BY and LIMIT for Nth Highest Salary in SQL:

    SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 3; -- Change this to the desired value of N

  5. Ranking and Partitioning for Nth Highest Salary in SQL:

    SELECT Salary FROM ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM Employees ) AS RankedSalaries WHERE SalaryRank = 3; -- Change this to the desired value of N

  6. Finding the Fourth Highest Salary in SQL Database:

    SELECT MAX(Salary) AS FourthHighestSalary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees)));

  7. Selecting Top N Salaries from a Table in SQL:

    SELECT EmployeeID, Salary FROM Employees ORDER BY Salary DESC FETCH FIRST 5 ROWS ONLY; -- Change this to the desired value of N

  8. Using DENSE_RANK() for Nth Highest Salary in SQL:

    SELECT Salary FROM ( SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM Employees ) AS RankedSalaries WHERE SalaryRank = 5; -- Change this to the desired value of N

  9. SQL Query to Find the Fifth Highest Salary in a Table:

    SELECT MAX(Salary) AS FifthHighestSalary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees))));

More Tags

linq-expressions azure-storage android-color drupal-8 http-status-code-403 apache-commons-csv node-sass ionic-native viewdidload toast

More Programming Guides

Other Guides

More Programming Examples