Find Nth Highest Salary in 5 Different Ways

here are five different ways to write a SQL query to find the nth highest salary from the employee table:

 Method 1: Using Subquery and Limit Clause

SELECT salary 
FROM employee 
ORDER BY salary DESC 
LIMIT n-1, 1;

In this method, we are first ordering the rows in the employee table in descending order based on the salary column. We then use the LIMIT clause to skip the first n-1 rows and select the next row. This will give us the nth highest salary in the table.

Method 2: Using the Outer Query and Subquery

SELECT DISTINCT salary 
FROM employee emp1 
WHERE n-1 = (
SELECT COUNT(DISTINCT salary) 
FROM employee emp2 
WHERE emp2.salary > emp1.salary
);

In this method, we are using a subquery to count the number of distinct salaries in the employee table that are greater than the salary in the outer query. We then use the WHERE clause to select the row with the count equal to n-1, which will give us the nth highest salary.

Method 3: Using Subquery with TOP

   SELECT TOP 1 salary FROM (
SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC
) AS emp ORDER BY salary ASC;

In this method, we use a subquery to select the top n distinct salaries in descending order. Then, we select the top 1 salary from this subquery in ascending order, which will give us the nth highest salary.

 

SELECT salary FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employee
) AS emp WHERE rn = n; 

      In this method, we use the ROW_NUMBER() function to assign a row number to each salary in descending order. Then, we select the salary where the row number is equal to n, which will give us the nth highest salary.

Method 5: Using a Self Join

SELECT DISTINCT emp1.salary FROM employee emp1 
LEFT JOIN employee emp2 ON emp1.salary < emp2.salary 
GROUP BY emp1.salary 
HAVING COUNT(DISTINCT emp2.salary) = n-1;

  In this method, we use a self join to join the employee table with itself on the condition that emp1.salary is less than emp2.salary. We then group by emp1.salary and count the number of distinct salaries in emp2 that are greater than emp1.salary. Finally, we select the salary from emp1 where the count is equal to n-1, which will give us the nth highest salary.

These five methods are just a few examples of how to write a SQL query to find the nth highest salary from the employee table. Depending on the specific database and use case, there may be other approaches as well.