How to Efficiently Find the Nth Maximum Salary in a Database?

Finding the nᵗʰ Maximum Salary from a Salary Table

Question: How can we efficiently find the third or nᵗʰ maximum salary from a salary table containing columns for employee ID, employee name, and employee salary?

Answer:

There are several methods to approach this problem:

Method 1: Row Number

SELECT Salary, EmpName
FROM
  (
   SELECT Salary, EmpName, ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
   FROM EMPLOYEE
   ) As A
WHERE A.RowNum IN (2,3)

This method uses the ROW_NUMBER() function to assign each salary a row number. We select salaries on row numbers that correspond to the desired positions (e.g., 2nd, 3rd).

Method 2: Sub Query

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )

This method uses a subquery to count the number of distinct salaries greater than each employee's salary. The nth maximum salary is for the employee whose count is equal to (n-1).

Method 3: Top Keyword

SELECT TOP 1 salary
FROM (
      SELECT DISTINCT TOP n salary
      FROM employee
      ORDER BY salary DESC
      ) a
ORDER BY salary

This method uses the TOP keyword to select the n highest salaries. The final ORDER BY clause ensures the true nth maximum salary is returned.

By using any of these methods, we can efficiently identify the third or nth maximum salary from a given salary table.

The above is the detailed content of How to Efficiently Find the Nth Maximum Salary in a Database?. For more information, please follow other related articles on the PHP Chinese website!