PostgreSQL Subquery: Get three maximum salaries

19. Write a query to get three maximum salaries.

Sample Solution:

Code:

-- This SQL query retrieves distinct salary values where each salary is in the top 3 distinct salary values in the employees table, ordered by salary in descending order.

SELECT DISTINCT salary -- Selects distinct salary values from the employees table (aliased as 'a')
FROM employees a -- Specifies the table from which to retrieve data (aliased as 'a'), in this case, the employees table
WHERE 3 >= ( -- 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 3 or fewer
    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 b -- Specifies the table from which to retrieve data (aliased as 'b'), in this case, the employees table
    WHERE a.salary <= b.salary -- Filters distinct salary values that are less than or equal to the salary of the current row (aliased as 'a')
) 
ORDER BY a.salary DESC; -- Orders the result set by salary in descending order

Explanation:

• This SQL query retrieves distinct salary values where each salary is in the top 3 distinct salary values in the employees table, ordered by salary in descending order.
• The outermost SELECT statement retrieves distinct salary values from the employees table (aliased as 'a').<./
• 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 3 or fewer.
• The subquery calculates the count of distinct salary values that are less than or equal to the salary of the current row.
• The ORDER BY clause orders the result set by salary in descending order.

Sample table: employees

Output:

pg_exercises=# SELECT DISTINCT salary
pg_exercises-# FROM employees a
pg_exercises-# WHERE  3 >= (SELECT COUNT(DISTINCT salary)
pg_exercises(# FROM employees b
pg_exercises(# WHERE a.salary <= b.salary)
pg_exercises-# ORDER BY a.salary DESC;
  salary
----------
 24000.00
 17000.00
 14000.00
(3 rows)

Practice Online

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

Previous: Write a subquery to display the information for all the departments where no employee is working.
Next: Write a query to get three minimum salaries.