w3resource

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:

SELECT DISTINCT salary 
FROM employees e1 
WHERE 5 = (SELECT COUNT(DISTINCT salary) 
FROM employees  e2 
WHERE e1.salary <= e2.salary); 

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.

What is the difficulty level of this exercise?