SQL Problem - Find 3rd highest employee salary

Problem Statement

Create an SQL query to retrieve all details of employees who receive the third-highest salary.
Ensure the query returns all columns from the employees table. Additional Requirement: Do not use the LIMIT keyword in your query.

Sample Input:

Table: employees:

Sample Output

Solution:

Approach 1: Using Sub Query:

select * from employees where salary = (

select distinct(salary) from employees  order by salary

desc limit 1 offset 2)

The subquery finds the third-highest distinct salary by ordering the salaries in descending order, skipping the first two, and then selecting the next one.

The main query then retrieves all employees who have this third-highest salary.

Approach 2: Using Nested Inner Queries

SELECT * FROM EMPLOYEES WHERE SALARY =(

    SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY <(

        SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY < (

            SELECT MAX(SALARY) FROM EMPLOYEES)  

    )

)

    

1. Innermost Subquery:
• SELECT MAX(salary) FROM employees finds the highest salary.
2. Middle Subquery:
• SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) finds the second-highest salary by looking for the maximum salary that is less than the highest salary found in the innermost subquery.
3. Outermost Subquery:
• SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)) finds the third-highest salary by looking for the maximum salary that is less than the second-highest salary found in the middle subquery.
4. Main Query:
• The main query selects all columns from the employees table where the salary matches the third-highest salary found in the outermost subquery.

Approach 2: Using CTE:

WITH RankedSalaries AS (

    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank

    FROM employees

)

SELECT *

FROM employees

WHERE salary = (

    SELECT salary

    FROM RankedSalaries

    WHERE rank = 3

);

In this alternative approach:

1. Common Table Expression (CTE): The CTE RankedSalaries assigns a rank to each distinct salary using the DENSE_RANK() window function.
2. Rank Filter: The subquery inside the WHERE clause selects the salary where the rank is 3.
3. Main Query: The outer query retrieves all employee details where their salary matches the third-highest salary.