PostgreSQL Subquery: Find the 5th maximum salary of all salaries

15. Write a subquery to find the 5th maximum salary of all salaries.

Sample Solution:

Code:

-- This SQL query retrieves distinct salary values where each salary is in the top 5 distinct salary values in the employees table.

SELECT DISTINCT salary -- Selects distinct salary values from the employees table (aliased as 'e1')
FROM employees e1 -- Specifies the table from which to retrieve data (aliased as 'e1'), in this case, the employees table
WHERE 5 = ( -- Filters the rows to include only those where the count of distinct salary values that are less than or equal to the salary of the current row is equal to 5
    SELECT COUNT(DISTINCT salary) -- Subquery: Counts the distinct salary values that are less than or equal to the salary of the current row
    FROM employees e2 -- Specifies the table from which to retrieve data (aliased as 'e2'), in this case, the employees table
    WHERE e1.salary <= e2.salary -- Filters distinct salary values that are less than or equal to the salary of the current row (aliased as 'e1')
);

Explanation:

• This SQL query retrieves distinct salary values where each salary is in the top 5 distinct salary values in the employees table.
• The outermost SELECT statement retrieves distinct salary values from the employees table (aliased as 'e1').
• The WHERE clause filters the rows to include only those where the count of distinct salary values that are less than or equal to the salary of the current row is equal to 5.
• The subquery calculates the count of distinct salary values that are less than or equal to the salary of the current row.

Sample table: employees

Output:

pg_exercises=# SELECT DISTINCT salary
pg_exercises-# FROM employees e1
pg_exercises-# WHERE 5 = (SELECT COUNT(DISTINCT salary)
pg_exercises(# FROM employees  e2
pg_exercises(# WHERE e1.salary <= e2.salary);
  salary
----------
 13000.00
(1 row)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL subquery to find the employee ID, first name, last name and salary of all employees whose salary is above the average salary for their departments.
Next: Write a subquery to find the 4th minimum salary of all the salaries.