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
)

Explanation:

• 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)  
    )
)

Explanation:

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 3: 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
 );

Explanation:

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.

This article presents three SQL query approaches to retrieve details of employees with the third-highest salary without using the LIMIT keyword. These include using a subquery, nested inner queries, and a Common Table Expression (CTE). Each approach includes an explanation and sample queries to illustrate how to achieve the desired result.